Hello, I'm using Excel 2007 and have some code which works fine, basically opening some work books from a list on a sheet and using "Union" to get some Non contiguous ranges and return the range data back to the originating book.
My problem is that if I put the "Get Some Ranges" code into a sub routine it fails to return any SrcRng data to the Main Sub saying SrcRng is empty. ( I need to put it into a sub routine as some of the work books have the same type of data but in different columns )
Eventually I intend using an if statement to decide what ranges to get depending on the open workbook.
Hope I've explained it ok, many thanks Robert
The following code works fine.
Code:
Sub OpenBookTest3()
Dim Obook As Workbook
Dim Dbook As Workbook
Dim sTrVal As String
Dim sTrVal2 As String
' OBook is already open
Sheets("Supplier Feeds").Activate
Set Obook = ActiveWorkbook
‘ Col B Contains a list of Suppliers workbook names
Range("b2").Select
Do Until IsEmpty(Cells(ActiveCell.Row, "B"))
sTrVal = ActiveCell.Value ‘ Supplier workbook
sTrVal2 = ActiveCell.Offset(0, -1).Value ‘ Supplier Name
Set Dbook = Workbooks.Open("C:\Users\Bob\Desktop\DataFolder\" & sTrVal & ".xlsx")
‘Get Some Ranges
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set RngA = Range("a2:a" & LastRow)
Set RngE = Range("e2:e" & LastRow)
Set RngF = Range("f2:f" & LastRow)
Set RngG = Range("g2:g" & LastRow)
Set RngY = Range("Y2:y" & LastRow)
Set RngZ = Range("z2:z" & LastRow)
'Grab Certain Columns
Set SrcRng = Application.Union(RngA, RngE, RngF, RngG, RngY, RngZ)
Set destRng = Obook.Sheets(sTrVal2).Cells(Rows.Count, "A").End(xlUp)(2)
Dbook.Activate
SrcRng.Copy _
Destination:=destRng
Dbook.Close SaveChanges:=False
Application.DisplayAlerts = False
ActiveCell.Offset(1, 0).Select
Loop
End Sub
The following is the bit of code I hope to put into a sub routine
Code:
‘Get Some Ranges
LastRow = Cells(Rows.Count, "a").End(xlUp).Row
Set RngA = Range("a2:a" & LastRow)
Set RngE = Range("e2:e" & LastRow)
Set RngF = Range("f2:f" & LastRow)
Set RngG = Range("g2:g" & LastRow)
Set RngY = Range("Y2:y" & LastRow)
Set RngZ = Range("z2:z" & LastRow)
'Grab Certain Columns
Set SrcRng = Application.Union(RngA, RngE, RngF, RngG, RngY, RngZ)
' Return to Sub OpenTest3