Macro to copy variable ammounts of data

[Mcflea]

What i would like to do is create a macro to copy data from column B and then paste the values into a table on another sheet. The Data in column B will not always be consistent. Sometimes it may be B2:B610 that has data that needs to be copied, sometimes it may just be B2:B3, and other occasions it may be B2:B374. The sheet the data is to be copied from is called "Formula" and the sheet it is to be pasted into is "Table". The values are to be copied into column A of "Table". I am fairly new to VBA and I am stumbling my way through, any help would be very much appreciated.
Thanks in advance

 

[wrightyrx7]

Try this:

Sub test()
    With Sheets("Formula")
    .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy Destination:=Sheets("Table").Range("A2")
    End With
End Sub

 

[Mcflea]

Hi wrightyrx7,
Appreciate the help but what appears in the past column is error #Ref! and in the cells is the formula =IF(#REF!<=D2,#REF!,""), so i suspect it is copying the formulas over rather than the values.

 

[wrightyrx7]

How about this:

Sub test()
    With Sheets("Formula")
        .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
        Sheets("Table").Range("A2").PasteSpecial Paste:=xlValues, _
        Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    
End Sub

 

[Mcflea]

Thanks that works brilliantly

 

[wrightyrx7]

No problem, thanks for the feedback