Utilising Range

[mikede]

Hi

I am reasonably new to vb programming in excel and having some trouble copying a specific range from one worksheet to another. I have tried two different methods both producing different issues. I want to copy everything one column up until the last row into a column in a separate worksheet

I initially tried
Set CopyRange = HeadingRange((1 & lLastRow), i)
to assign the range of data I wanted copied but no data showed up in the other colomn when I copied across.

I then tried
Set CopyRange = Sheets("w").Range(Cells(2, i), Cells(lLastRow, i)).Select but this created a susbsript out of range message. Not sure how much supporting code I need to provide but have placed a little below. Apologies for the clunky nature of it

Love to get some help as have been staring blankly at the screen for a number of days with no inspiration coming.

[vba]
For Each w In Worksheets
w.Select
With w

lLastRow = Get_Last_Row(.Cells)
Set HeadingRange = .Range(.Cells(1, 1), .Cells(lLastRow, 1))
lLastCol = HeadingRange.End(xlToRight).Column
i = 1
Do While i <= lLastCol
If HeadingRange(1, i) = "/summaryRecord/iacIdentifier" Then
Set CopyRange = Sheets("w").Range(Cells(2, i), Cells(lLastRow, i)).Select
' Set CopyRange = HeadingRange((1 & lLastRow), i)

CopyRange.Copy Destination:=Sheets("rightdata").Range("A" & lLastRow)

End If
[/vba]
...........

[Bob Phillips]

[vba]

For Each w In Worksheets

lnextRow = Get_Last_Row(Sheets("rightdata").Cells)
With w

lLastRow = Get_Last_Row(.Cells)
Set headingrange = .Range(.Cells(1, 1), .Cells(lLastRow, 1))
lLastCol = headingrange.Rows(1).End(xlToRight).Column
i = 1
Do While i <= lLastCol
If headingrange(1, i) = "/summaryRecord/iacIdentifier" Then
Set CopyRange = .Range(.Cells(2, i), .Cells(lLastRow, i))
' Set CopyRange = HeadingRange((1 & lLastRow), i)

lnextRow = lnextRow + 1
CopyRange.Copy Destination:=Sheets("rightdata").Range("A" & lnextRow)
End If
Loop
End With
Next w
[/vba]