CODEWITHSUNDEEP
vbaexcel
Seidhe
Seidhe

Reputation: 83

Finding a value and then copying a column in which it occurs

I am trying to write a macro which will find a value "Spot price" in a header of a table2 and then take the whole column where that value is to copy and paste it (as values and not formulas). I need to do it because in that column I have a reference to external workbook and I need to share this worksheet without sharing the other workbook. This is what I wrote

Sub row()
Dim firstrow As Range
Dim ColumnToPaste As Range
Dim copy As Range

Set firstrow = Worksheets("Raw Data").ListObjects("Table2").HeaderRowRange
Set ColumnToPaste = firstrow.Find("Spot price", , xlValues, xlWhole).Columns(1)

With Worksheets("Raw Data").Range(ColumnToPaste)
    .PasteSpecial Paste:=xlPasteValues
End With

End Sub

The error occurs on this line (Application-defined or object-defined error):

With Worksheets("Raw Data").Range(ColumnToPaste)

I think I'm using wrong range argument but not sure how to write it properly.

Upvotes: 0

Views: 35

Answers (1)

Xabier
Xabier

Reputation: 7735

How about the following, instead of copying the full column, specify the range:

Sub row2()
Dim ws As Worksheet: Set ws = Sheets("Raw Data")
'declare and set your worksheet, amend as required
Dim firstrow As Range
Dim ColumnToPaste As Range
Dim LastRow As Long

Set firstrow = ws.ListObjects("Table2").HeaderRowRange
Set ColumnToPaste = firstrow.Find("Spot price", , xlValues, xlWhole).Columns
col = ColumnToPaste.Column
LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
'get the last row with data on Column A
With ws
    .Range(.Cells(1, col), .Cells(LastRow, col)).Copy
    .Range(.Cells(1, col), .Cells(LastRow, col)).PasteSpecial xlPasteValues
End With
End Sub

Upvotes: 1

Related Questions