PDA

View Full Version : Utilising Range



mikede
12-05-2009, 09:14 PM
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.


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

...........

Bob Phillips
12-06-2009, 03:29 AM
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