Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Since the files will be in different locations,
you can list them in column B like:
C:\temp\Users\test\Desktop\Labour\test\11000.xlsx
C:\temp\Users\test\Desktop\Labour\test\11001.xlsx
C:\temp\Users\test\Desktop\Labour\test\11002.xlsx
C:\temp\Users\test\Desktop\Labour\test\11003.xlsx
C:\temp\Users\test\Desktop\Labour\test\11004.xlsx
C:\temp\Users\test\Desktop\Labour\test\11005.xlsx
then, the macro can loop through this list and collect the sheets:
Option Explicit
Sub Consolidate_Sheets()
Dim ShtNo As String, NewWb, wbName
Dim fPath
Dim Sht, fso, fld, File
Dim R As Integer, nRows As Integer
Application.ScreenUpdating = False
'
nRows =
Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(1).Range("B1:B65000"))
'
' Determine sheet to copy
'
ShtNo = ThisWorkbook.Sheets(1).Range("A1").Value
If (ShtNo & "X" = "X") Then
MsgBox "No Sheet Specified"
Exit Sub
End If
'
' Define new Workbook Name
'
NewWb = "week " & ShtNo & " update"
'
Workbooks.Add
ActiveWorkbook.SaveAs _
Filename:=fPath & NewWb & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
'
Set fso = CreateObject("Scripting.FileSystemObject")
'
' Loop through files in column B
'
For R = 1 To nRows
If (ThisWorkbook.Sheets(1).Cells(R, "B").Value & "X" <> "X") Then
If (Not fso.fileexists(ThisWorkbook.Sheets(1).Cells(R, "B").Value))
Then
MsgBox "Could not located file:" & Chr(13) &
ThisWorkbook.Sheets(1).Cells(R, "B").Value
Else
Set File = fso.getfile(ThisWorkbook.Sheets(1).Cells(R,
"B").Value)
wbName = Replace(UCase(File.Name), ".XLSX", "") 'Remove file
extension
Workbooks.Open Filename:=File.Path
Workbooks(File.Name).Sheets(ShtNo).Copy _
After:=Workbooks(NewWb & ".xlsm").Sheets(Workbooks(NewWb &
".xlsm").Sheets.Count)
ActiveSheet.Name = wbName
Workbooks(File.Name).Close savechanges:=False
End If
End If
Next R
'
Application.ScreenUpdating = True
'
Workbooks(NewWb & ".xlsm").Close savechanges:=True
End Sub
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: dza49
>To: excel-macros@googlegroups.com
>Cc: schreiner_p...@att.net
>Sent: Tuesday, November 4, 2014 9:32 AM
>Subject: Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named
>tabs into one spreadsheet
>
>
>
>Hello!
>
>With regards to consolidation file location point, yes that path
>(C:\Users\test\Desktop\Labour\test) is where I'd like the file to be saved to
>in the end.
>
>As for the "source" files if you will, (aka the files maintained by stores).
>These files will be saved in different folders on the network and yes, I will
>want to consolidate specific sheets from each store's file. Each file will
>have a unique name but each of the sheets will be labeled the same way (ie
>week number like 26, 27, 28, etc.)
>
>For the purpose of this test, we can use the assume that the source files and
>the consolidation macro will all reside in the same directory
>(C:\Users\test\Desktop\Labour\test). The file names are 11000,xlsx,
>11001,xlsx and 11002.xlsx. Becuase in the end the source files will reside in
>different locations on the network having a macro that consolidates these
>specific files would be great!
>
>Again, the only area I'm struggling with is how to use a cell reference in a
>macro to then dictate which sheets need to be pulled into the consolidation
>file.
>
>Thanks for your help!
>
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Hello! With regards to consolidation file location point, yes that path (C:\Users\test\Desktop\Labour\test) is where I'd like the file to be saved to in the end. As for the "source" files if you will, (aka the files maintained by stores). These files will be saved in different folders on the network and yes, I will want to consolidate specific sheets from each store's file. Each file will have a unique name but each of the sheets will be labeled the same way (ie week number like 26, 27, 28, etc.) For the purpose of this test, we can use the assume that the source files and the consolidation macro will all reside in the same directory (C:\Users\test\Desktop\Labour\test). The file names are 11000,xlsx, 11001,xlsx and 11002.xlsx. Becuase in the end the source files will reside in different locations on the network having a macro that consolidates these specific files would be great! Again, the only area I'm struggling with is how to use a cell reference in a macro to then dictate which sheets need to be pulled into the consolidation file. Thanks for your help! On Tuesday, 4 November 2014 13:52:07 UTC, Paul Schreiner wrote: > > I'm sure I can put together something that will work cleanly. > > Rather than make assuptions though, I'll ask a couple of questions: > > in the folder: > C:\Users\test\Desktop\Labour\test > > will you be consolidating specific sheets from ALL of the files in this > location > (that do now begin with "Week.." or all are type .xlsx?) > > We can specify the files on the worksheet: > 11000 > 11001 > 11002 > > or we can simply process all files in the folder. > > I'll make some sample files for testing and then put together a macro for > you. > > *Paul* > - > > > > > > > > *“Do all the good you can,By all the means you can,In all the ways you > can,In all the places you can,At all the times you can,To all the people > you can,As long as ever you can.” - John Wesley* > ----------------- > >*From:* dza49 > > *To:* excel-...@googlegroups.com > *Sent:* Tuesday, November 4, 2014 8:11 AM > *Subject:* $$Excel-Macros$$ Cell Reference to consolidate specifically > named tabs into one spreadsheet > > Hello, > > I have been given a task where I have say 10 spreadsheets located on the > network which have the same filename and tab naming convention. Each > spreadsheet will have a tab for each week of the year that contains reports > that I'd like to consolidate. For example, each spreasdsheet will have the > week number listed as tab name and I want to pull the same week's > information into one file to have one spreadsheet with each location's > report for the week. > > Here is the VBA code for the very simple flow that I want to replicate on > larger scale: > > http://pastebin.com/99WG6nVz > > It basically opens each location's spreadsheet, grabs week 27's report and > then consolidates them into one file that I save as a new copy called "week > 27 update.xlsm". > > Here is where I need some help. > > I'd like to set up the original "MACRO.xlsm" file with a reference to the > week number in a cell so that I could select which tabs to copy over. For > example, the above macro pulls week 27 from each of the spreadsheets, I'd > like to make this dynamic in such that I could enter the week number and > then use that cell value to dictate which week would be copied over. For > clarification, each tab is named at number like 27, 28, 29, etc. The last > bit of custom work would be to use that same cell value and save the file > of the newly consolidated files to a location. The above example is "week > XX update.xlsm" where XX is the cell value. > > I hope that all makes sense. > > Thank you in advance! > > Dza > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post co
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Here's what I came up with.
Let me know if you need additional explanation.
Option Explicit
Sub Consolidate_Sheets()
Dim ShtNo As String, NewWb, wbName
Dim fPath
Dim Sht, fso, fld, File
Application.ScreenUpdating = False
'
fPath = "C:\temp\Users\test\Desktop\Labour\test\"
'
' Determine sheet to copy
'
ShtNo = ThisWorkbook.Sheets(1).Range("A1").Value
If (ShtNo & "X" = "X") Then
MsgBox "No Sheet Specified"
Exit Sub
End If
'
' Define new Workbook Name
'
NewWb = "week " & ShtNo & " update"
'
Workbooks.Add
ActiveWorkbook.SaveAs _
Filename:=fPath & NewWb & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
CreateBackup:=False
'
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fPath)
'
' Loop through files in fPath, looking for .XLSX files
'
For Each File In fld.Files
If (UCase(Right(File.Name, 4)) = "XLSX") Then
wbName = Replace(UCase(File.Name), ".XLSX", "") 'Remove file
extension
Workbooks.Open Filename:=fPath & File.Name
Workbooks(File.Name).Sheets(ShtNo).Copy _
After:=Workbooks(NewWb & ".xlsm").Sheets(Workbooks(NewWb &
".xlsm").Sheets.Count)
ActiveSheet.Name = wbName
Workbooks(File.Name).Close savechanges:=False
End If
Next File
'
Application.ScreenUpdating = True
'
Workbooks(NewWb & ".xlsm").Close savechanges:=True
End Sub
Paul
-
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-
From: Paul Schreiner
>To: "excel-macros@googlegroups.com"
>Sent: Tuesday, November 4, 2014 8:51 AM
>Subject: Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named
>tabs into one spreadsheet
>
>
>
>I'm sure I can put together something that will work cleanly.
>
>Rather than make assuptions though, I'll ask a couple of questions:
>
>in the folder:
>C:\Users\test\Desktop\Labour\test
>
>will you be consolidating specific sheets from ALL of the files in this
>location
>(that do now begin with "Week.." or all are type .xlsx?)
>
>We can specify the files on the worksheet:
>11000
>11001
>11002
>
>or we can simply process all files in the folder.
>
>I'll make some sample files for testing and then put together a macro for you.
>
>Paul
>---------
>“Do all the good you can,
>By all the means you can,
>In all the ways you can,
>In all the places you can,
>At all the times you can,
>To all the people you can,
>As long as ever you can.” - John Wesley
>-
>
>
> From: dza49
>>To: excel-macros@googlegroups.com
>>Sent: Tuesday, November 4, 2014 8:11 AM
>>Subject: $$Excel-Macros$$ Cell Reference to consolidate specifically named
>>tabs into one spreadsheet
>>
>>
>>
>>Hello,
>>
>>I have been given a task where I have say 10 spreadsheets located on the
>>network which have the same filename and tab naming convention. Each
>>spreadsheet will have a tab for each week of the year that contains reports
>>that I'd like to consolidate. For example, each spreasdsheet will have the
>>week number listed as tab name and I want to pull the same week's
information into one file to have one spreadsheet with each location's report
for the week.
>>
>>Here is the VBA code for the very simple flow that I want to replicate on
>>larger scale:
>>
>>http://pastebin.com/99WG6nVz
>>
>>It basically opens each location's spreadsheet, grabs week 27's report and
>>then consolidate
Re: $$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
I'm sure I can put together something that will work cleanly. Rather than make assuptions though, I'll ask a couple of questions: in the folder: C:\Users\test\Desktop\Labour\test will you be consolidating specific sheets from ALL of the files in this location (that do now begin with "Week.." or all are type .xlsx?) We can specify the files on the worksheet: 11000 11001 11002 or we can simply process all files in the folder. I'll make some sample files for testing and then put together a macro for you. Paul - “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley - > > From: dza49 >To: excel-macros@googlegroups.com >Sent: Tuesday, November 4, 2014 8:11 AM >Subject: $$Excel-Macros$$ Cell Reference to consolidate specifically named >tabs into one spreadsheet > > > >Hello, > >I have been given a task where I have say 10 spreadsheets located on the >network which have the same filename and tab naming convention. Each >spreadsheet will have a tab for each week of the year that contains reports >that I'd like to consolidate. For example, each spreasdsheet will have the >week number listed as tab name and I want to pull the same week's information >into one file to have one spreadsheet with each location's report for the week. > >Here is the VBA code for the very simple flow that I want to replicate on >larger scale: > >http://pastebin.com/99WG6nVz > >It basically opens each location's spreadsheet, grabs week 27's report and >then consolidates them into one file that I save as a new copy called "week 27 >update.xlsm". > >Here is where I need some help. > >I'd like to set up the original "MACRO.xlsm" file with a reference to the week >number in a cell so that I could select which tabs to copy over. For example, >the above macro pulls week 27 from each of the spreadsheets, I'd like to make >this dynamic in such that I could enter the week number and then use that cell >value to dictate which week would be copied over. For clarification, each tab >is named at number like 27, 28, 29, etc. The last bit of custom work would be >to use that same cell value and save the file of the newly consolidated files >to a location. The above example is "week XX update.xlsm" where XX is the >cell value. > >I hope that all makes sense. > >Thank you in advance! > >Dza > > -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not respon
$$Excel-Macros$$ Cell Reference to consolidate specifically named tabs into one spreadsheet
Hello, I have been given a task where I have say 10 spreadsheets located on the network which have the same filename and tab naming convention. Each spreadsheet will have a tab for each week of the year that contains reports that I'd like to consolidate. For example, each spreasdsheet will have the week number listed as tab name and I want to pull the same week's information into one file to have one spreadsheet with each location's report for the week. Here is the VBA code for the very simple flow that I want to replicate on larger scale: http://pastebin.com/99WG6nVz It basically opens each location's spreadsheet, grabs week 27's report and then consolidates them into one file that I save as a new copy called "week 27 update.xlsm". Here is where I need some help. I'd like to set up the original "MACRO.xlsm" file with a reference to the week number in a cell so that I could select which tabs to copy over. For example, the above macro pulls week 27 from each of the spreadsheets, I'd like to make this dynamic in such that I could enter the week number and then use that cell value to dictate which week would be copied over. For clarification, each tab is named at number like 27, 28, 29, etc. The last bit of custom work would be to use that same cell value and save the file of the newly consolidated files to a location. The above example is "week XX update.xlsm" where XX is the cell value. I hope that all makes sense. Thank you in advance! Dza -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
