#1
|
|||
|
|||
Data got getting returned from Sub Routine
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 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 Last edited by Hwyn; 01-18-2012 at 06:54 AM. Reason: Code Wrap |
#2
|
||||
|
||||
Might I suggest you wrap your code with code tags ( makes it easier to copy) ?
Just edit your post, click "go advanced", select your code and click the # button, then save. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Ooops Sorry ..Thanks for the Quote Tip
Robert |
#4
|
|||
|
|||
search will not work if the range is not contiguous, you need another approach, you range can be split in 3 contiguous ranges.
|
#5
|
|||
|
|||
Same for copy-paste ranges...
|
#6
|
|||
|
|||
Quote:
Regards Robert |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro or routine required | evanhughes | Outlook | 0 | 11-18-2011 02:56 AM |
Email returned from a different address then the original destination | Webfaction | Outlook | 1 | 04-25-2011 03:16 PM |
returned mail put in tray and not in inbox | tcreek | Outlook | 0 | 10-19-2010 12:06 PM |
The Message interface returned an unknown error. If the problem presists, restart out | RolandVet | Outlook | 2 | 04-18-2010 04:54 AM |
Word crashing on "sort" routine | Kayale | Word | 0 | 04-12-2010 04:54 PM |