VBA Copy column and paste formulas only - not values - to next column

Question

Spreadsheet column B has a random mix of values and formulas. I want to use VBA to copy that column into the next column and only copy the formulas (not the values) into column C. I was able to use the following VBA to some success but it copies every column past B to infinity (where I want it to stop copying after the first column).

Sub Copy_Column_Formulas_NOvalues()
'
' Copy_Column_Formulas_NOvalues Macro
'
Dim oSheet As Worksheet
Dim rng As Range
Dim cel As Range
Set oSheet = Sheets("Sheet1")
With oSheet
    Set rng = .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp))
    For Each cel In rng
        If Left(cel.Formula, 1) = "=" Then
            Range(cel.Offset(, 1), cel.Offset(, 1).End(xlToRight)).FormulaR1C1 = cel.FormulaR1C1
            End If
    Next cel
End With
End Sub

remove .End(xlToRight). Actually change Range(cel.Offset(, 1), cel.Offset(, 1).End(xlToRight)) to cel.Offset(,1) — Scott Holtzman
– Scott Holtzman, Commented May 18, 2017 at 15:02

Gowtham Shiva · Accepted Answer · 2017-05-18 16:41:56Z

0

Try this simple macro and let me know if it works,

Sub Copy_Column_Formulas_NOvalues()
Dim i As Long, j As Long
For i = 1 To Sheets("Sheet3").Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, 2).HasFormula Then
   Cells(i, 3) = Cells(i, 2).Formula
End If
Next i
End Sub

Change the Sheet3 name as per your needs.

answered May 18, 2017 at 16:41

Gowtham Shiva

3,8852 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ssttuuky Over a year ago

I changed the "Sheet3" to my sheet name. I changed "B" to the column I wanted to copy "W" (copied to "X") and ran it. It took awhile but did not copy as desired. It looks like it still copied B into C.

Gowtham Shiva Over a year ago

The IF condition still checks the column B value only. it should search in column W and paste it in X. Alter your formula accordingly

ssttuuky Over a year ago

I changed the "Sheet3" to my sheet name. I changed "B" to the column I wanted to copy "W" (copied to "X") and ran it. It took awhile but did not copy as desired. It looks like it still copied B into C. So I changed the "2" into the column number "23" (which is Column W) and the "3" to "24" (which is Column X) and IT WORKED.... Takes a little while but does what I needed!!!

Gowtham Shiva Over a year ago

@ssttuuky Thats great !!

ssttuuky Over a year ago

Can you use letters instead of numbers in the if statement?

|

Collectives™ on Stack Overflow

VBA Copy column and paste formulas only - not values - to next column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related