A while back, someone on these boards pointed me to this script ( https://sites.google.com/a/madrocke.../merge-functions/consolidate-wbs-to-one-sheet ) which has really served my purposes well.
It takes the first sheet of all workbooks within a folder and and copies all rows into my master and stacks the rows one over the other. Perfect. Basically consolidating all data into one master sheet for me.
Now I want to take that to the next step.
My "Master" now has 12 tabs.. JAN, FEB, MAR etc etc
My 20 to 30 or so source workbooks each also have the same 12 tabs.
I want to consolidate the JAN tab of each source workbook into my "Master" JAN..... same for FEB into my MASTER FEB.. and so on.
So keep functionality almost exactly as is but consolidate EACH tab to the corresponding MASTER tab, all 12 tabs to consolidate with 1 click of the macro
Can anyone help with tweaking the existing code ? Thanks in advance, much appreciate !
</STRONG>
It takes the first sheet of all workbooks within a folder and and copies all rows into my master and stacks the rows one over the other. Perfect. Basically consolidating all data into one master sheet for me.
Now I want to take that to the next step.
My "Master" now has 12 tabs.. JAN, FEB, MAR etc etc
My 20 to 30 or so source workbooks each also have the same 12 tabs.
I want to consolidate the JAN tab of each source workbook into my "Master" JAN..... same for FEB into my MASTER FEB.. and so on.
So keep functionality almost exactly as is but consolidate EACH tab to the corresponding MASTER tab, all 12 tabs to consolidate with 1 click of the macro
Can anyone help with tweaking the existing code ? Thanks in advance, much appreciate !
Code:
[FONT=courier new]Option Explicit
Sub Consolidate()
[B]'Author: Jerry Beaucaire'
'Date: 9/15/2009 (2007 compatible) (updated 4/29/2011)
'Summary: Merge files in a specific folder into one master sheet (stacked)
' Moves imported files into another folder
[/B]
Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet
[B]'Setup</STRONG>
Application.ScreenUpdating = False [B]'speed up macro execution</STRONG>
Application.EnableEvents = False [B]'turn off other macros for now</STRONG>
Application.DisplayAlerts = False [B]'turn off system messages for now</STRONG>
Set wsMaster = ThisWorkbook.Sheets("[B]Master</STRONG>") [B]'sheet report is built into</STRONG>
With wsMaster
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
.UsedRange.Offset(1).EntireRow.Clear
NR = 2
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 [B]'appends data to existing data</STRONG>
End If
[B]'Path and filename (edit this section to suit)</STRONG>
fPath = "[B]C:\2011\Files\</STRONG>" [B]'remember final \ in this string</STRONG>
fPathDone = fPath & "Imported\" [B]'remember final \ in this string</STRONG>
On Error Resume Next
MkDir fPathDone [B]'creates the completed folder if missing</STRONG>
On Error GoTo 0
fName = Dir(fPath & "[B]*.xls*</STRONG>") [B]'listing of desired files, edit filter as desired</STRONG>
[B]
'Import a sheet from found files</STRONG>
Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then [B]'don't reopen this file accidentally</STRONG>
Set wbData = Workbooks.Open(fPath & fName) [B]'Open file</STRONG>
[B] 'This is the section to customize, replace with your own action code as needed
</STRONG> LR = Range("A" & Rows.Count).End(xlUp).Row [B]'Find last row</STRONG>
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
wbData.Close False [B]'close file</STRONG>
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 [B]'Next row</STRONG>
Name fPath & fName As fPathDone & fName [B]'move file to IMPORTED folder</STRONG>
End If
[/FONT][FONT=courier new] fName = Dir [B]'ready next filename</STRONG>
[/FONT][FONT=courier new] Loop
End With
ErrorExit: [B]'Cleanup</STRONG>
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True [B]'turn system alerts back on</STRONG>
Application.EnableEvents = True [B]'turn other macros back on</STRONG>
Application.ScreenUpdating = True [B]'refreshes the screen</STRONG>
End Sub
[/FONT]
[/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B][/B]