Hello everyone,
Basically I'm trying to consolidate information from many spreadsheets into 1 with a few twists. I have my new template spreadsheet (#1), another "schedule" spreadsheet where each line is a seperate order (#2). This spreadsheet includes a cell on each line with the filename that references another spreadsheet that has the information about that order (#3). There are 633 different lines/orders in spreadsheet #2, therefore 633 different spreadsheets with order information (#3). Essentially I want to pull all of the lines from the #3 spreadsheets into 1, but I only want specific cells/columns from spreadsheet #3, and want them to be inserted into specific cells/columns in spreadsheet #1. The additional catch is for each line that is written into spreadsheet #1 I want to include information from the line in spreadsheet #2. I'm hoping this explanation is somewhat clear.
When a macro is run on spreadsheet 1, it should open spreadsheet #2, look at the first line of data (after the headers) and open the file referenced, and pull some information into spreadsheet 1 + some information information from spreadsheet 2 on that same line which is why it has to be a for loop for each line instead of just selecting the whole sheet as a range. I also don't want to include the "totals" information at the bottom of each spreadsheet.
I had some introduction to VB about 7 years ago, haven't done much with it since...so I'm not sure why I've been handed this task, but none the less it's on my plate. I've included 4 spreadsheets (with the values changed), as well as the very tiny amount of code I've grabbed from other sites and slightly modified but can't figure out how to get much further. ***apparently I can't attach my workbooks, I'll send them to anybody who's willing to help though
I'll start with assuming all files are in the same directory, but eventually I'll get to the point where the #3 spreadsheets are in different directories that can be located based on the filename in spreadsheet #2. I'll also get to the point where I'd like to be able to point spreadsheet #1 at different "schedules" (ie #2 spreadsheets), but I'll get to that later.
I'm obviously not looking for someone to write the entire code for me, but any help on structure and the language to use would be greatly appreciated. I've got about 6 VBA books in front of me to look up anything...slowly workin through 'em. If you're able to help and you're near waterloo or Hamilton Ontario, I'll buy you a beer!...haha
Code used: *note, the section within the FOR loop has been changed to comments because it was in the code I grabbed from another site but it's not exactly what I'm looking for but I didn't want to delete it.
Trying to figure out how to use the "bbcode" tag to make this easier to read...but until then.
Sub CombineSheetsFromAllFilesInADirectory()
'Uses methods found in http://vbaexpress.com/kb/getarticle.php?kb_id=151 and
' http://vbaexpress.com/kb/getarticle.php?kb_id=221
Dim Path As String 'string variable to hold the path to look through
Dim FileName As String 'temporary filename string variable
Dim tWB As Workbook 'temporary workbook (each in directory)
Dim tWS As Worksheet 'temporary worksheet variable
Dim mWB As Workbook 'master workbook
Dim aWS As Worksheet 'active sheet in master workbook
Dim RowCount As Long 'Rows used on master sheet
Dim uRange As Range 'usedrange for each temporary sheet
'***** Set folder to cycle through *****
Path = ThisWorkbook.Path 'Change as needed, ie "C:\"
Application.EnableEvents = False 'turn off events
Application.ScreenUpdating = False 'turn off screen updating
Set mWB = ThisWorkbook 'create a new one-worksheet workbook
Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
Path = Path & Application.PathSeparator 'add "\"
End If
FileName = "GSDC0809Q2MGCS-Service Ontario Contact Centre - eChannel Operations.xls" 'set first file's name to filename variable
Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
For Each tWS In tWB.Worksheets 'loop through each sheet
'Set uRange = tWS.Range("G10", tWS.Cells(tWS.UsedRange.Row + tWS.UsedRange.Rows _
'.Count - 1, tWS.UsedRange.Column + tWS.UsedRange.Columns.Count - 1)) 'set used range
'aWS.Range("B" & RowCount + 1).Resize(uRange.Rows.Count, uRange.Columns.Count).Value _
'= uRange.Value 'move data from temp sheet to data sheet
RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
Next 'tWS
tWB.Close False 'close temporary workbook without saving
mWB.Sheets(1).Select 'select first data sheet on master workbook
Application.EnableEvents = True 're-enable events
Application.ScreenUpdating = True 'turn screen updating back on
'Clear memory of the object variables
Set tWB = Nothing
Set tWS = Nothing
Set mWB = Nothing
Set aWS = Nothing
Set uRange = Nothing
End Sub
Basically I'm trying to consolidate information from many spreadsheets into 1 with a few twists. I have my new template spreadsheet (#1), another "schedule" spreadsheet where each line is a seperate order (#2). This spreadsheet includes a cell on each line with the filename that references another spreadsheet that has the information about that order (#3). There are 633 different lines/orders in spreadsheet #2, therefore 633 different spreadsheets with order information (#3). Essentially I want to pull all of the lines from the #3 spreadsheets into 1, but I only want specific cells/columns from spreadsheet #3, and want them to be inserted into specific cells/columns in spreadsheet #1. The additional catch is for each line that is written into spreadsheet #1 I want to include information from the line in spreadsheet #2. I'm hoping this explanation is somewhat clear.
When a macro is run on spreadsheet 1, it should open spreadsheet #2, look at the first line of data (after the headers) and open the file referenced, and pull some information into spreadsheet 1 + some information information from spreadsheet 2 on that same line which is why it has to be a for loop for each line instead of just selecting the whole sheet as a range. I also don't want to include the "totals" information at the bottom of each spreadsheet.
I had some introduction to VB about 7 years ago, haven't done much with it since...so I'm not sure why I've been handed this task, but none the less it's on my plate. I've included 4 spreadsheets (with the values changed), as well as the very tiny amount of code I've grabbed from other sites and slightly modified but can't figure out how to get much further. ***apparently I can't attach my workbooks, I'll send them to anybody who's willing to help though
I'll start with assuming all files are in the same directory, but eventually I'll get to the point where the #3 spreadsheets are in different directories that can be located based on the filename in spreadsheet #2. I'll also get to the point where I'd like to be able to point spreadsheet #1 at different "schedules" (ie #2 spreadsheets), but I'll get to that later.
I'm obviously not looking for someone to write the entire code for me, but any help on structure and the language to use would be greatly appreciated. I've got about 6 VBA books in front of me to look up anything...slowly workin through 'em. If you're able to help and you're near waterloo or Hamilton Ontario, I'll buy you a beer!...haha
Code used: *note, the section within the FOR loop has been changed to comments because it was in the code I grabbed from another site but it's not exactly what I'm looking for but I didn't want to delete it.
Trying to figure out how to use the "bbcode" tag to make this easier to read...but until then.
Sub CombineSheetsFromAllFilesInADirectory()
'Uses methods found in http://vbaexpress.com/kb/getarticle.php?kb_id=151 and
' http://vbaexpress.com/kb/getarticle.php?kb_id=221
Dim Path As String 'string variable to hold the path to look through
Dim FileName As String 'temporary filename string variable
Dim tWB As Workbook 'temporary workbook (each in directory)
Dim tWS As Worksheet 'temporary worksheet variable
Dim mWB As Workbook 'master workbook
Dim aWS As Worksheet 'active sheet in master workbook
Dim RowCount As Long 'Rows used on master sheet
Dim uRange As Range 'usedrange for each temporary sheet
'***** Set folder to cycle through *****
Path = ThisWorkbook.Path 'Change as needed, ie "C:\"
Application.EnableEvents = False 'turn off events
Application.ScreenUpdating = False 'turn off screen updating
Set mWB = ThisWorkbook 'create a new one-worksheet workbook
Set aWS = mWB.ActiveSheet 'set active sheet variable to only sheet in mWB
If Right(Path, 1) <> Application.PathSeparator Then 'if path doesnt end in "\"
Path = Path & Application.PathSeparator 'add "\"
End If
FileName = "GSDC0809Q2MGCS-Service Ontario Contact Centre - eChannel Operations.xls" 'set first file's name to filename variable
Set tWB = Workbooks.Open(FileName:=Path & FileName) 'open file, set to tWB variable
For Each tWS In tWB.Worksheets 'loop through each sheet
'Set uRange = tWS.Range("G10", tWS.Cells(tWS.UsedRange.Row + tWS.UsedRange.Rows _
'.Count - 1, tWS.UsedRange.Column + tWS.UsedRange.Columns.Count - 1)) 'set used range
'aWS.Range("B" & RowCount + 1).Resize(uRange.Rows.Count, uRange.Columns.Count).Value _
'= uRange.Value 'move data from temp sheet to data sheet
RowCount = RowCount + uRange.Rows.Count 'increase rowcount accordingly
Next 'tWS
tWB.Close False 'close temporary workbook without saving
mWB.Sheets(1).Select 'select first data sheet on master workbook
Application.EnableEvents = True 're-enable events
Application.ScreenUpdating = True 'turn screen updating back on
'Clear memory of the object variables
Set tWB = Nothing
Set tWS = Nothing
Set mWB = Nothing
Set aWS = Nothing
Set uRange = Nothing
End Sub
Last edited:
