Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


VB Excel Copy all workbooks in one with separate sheets

Posted on 2010-09-23
Medium Priority
Last Modified: 2012-05-10
Hi all!

This is mainly a copy/paste question, and want to use VB activeX controls ...

What I have: several workbooks named WeeklyReport_MMDDYYYY.xlsx of course a new one each week is generated.

What I want to do:
Have a main Workbook called LocalReport.xlsm
If possible to get a pop-up asking which file (WeeklyReport_xxxx) I want to use

Then copy all the data from the first sheet of the choosen "WeeklyReport_ " workbook and paste to my "LocalReport "workbook in a new created sheet called "MMDDYYYY"

In another words, I have workbooks:
WeeklyReport_01252010, WeeklyReport_02282010, WeeklyReport_03192010, ...
and i want only 1 workbook: "LocalReport" with sheets "01252010", "02282010", "03192010",...
Of course each sheet will contain data from their respective original workbook (1st sheet)

Later I will need to apply filter and only take colums a,c,f,g,h.

Would you please provide the code for that?

Thanks for your support.
Question by:Lommag
LVL 24

Expert Comment

ID: 33744836

Author Comment

ID: 33752285
Broomee9 it sounds interesting, but I'm a newbie and only copy/adapt piece of code.

For now I just copy/paste the full code on a macro, running it: it only asks for a folder, that's all.
So I really need an explanation how to do it.

Again I'm a beginner, so I used to record macro, adapt piece of code, put some activX control such as command button or text box and affect a macro. Some basis are missing in my mind.

So it's more that welcome if you can help on thay

Author Comment

ID: 33754082
I really need help.
I'm not able to adapt the code.

 My code must be on LocalReport.xlsm workbook
I just need to be able to browse and choose a file, eg: WeeklyReport_MMDDYYYY
Then copy sheet1 of WeeklyReport_MMDDYYYY to LocalReport.xlsm in a nex sheet called MMDDYYYY

When it will work, I will adapt the code to run filter.

Thanks for your support
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 24

Expert Comment

ID: 33754557
Attached is the workbook with Dave's code in it from the link above.

You will first be prompted whether you want to consolidate a single folder (true) or a single file (false).  So pick enter true or false depending on your requirement.

Then you will be asked whether you want all the data on one sheet (true) or a new sheet in each of the target sheets (false).  This second option should be used only if you selected True in the first option, and if you want each workbook to have one master consolidated sheet.  If you want to combine everything into one sheet, then leave the default as true.

Then you will be prompted to either browse to the file or the folder, depending on what you selected in the first option.

Then you just let it run and the combined file will be created.


Author Comment

ID: 33767975
Broomee9, thanks for your help, but again this is not what I'm looking for.

In each WeeklyReport_MMDDYYYY workbook I have 3 sheets.
From always the same general workbook call 'LocalReport.xlsm", running the macro, I just need to be able to choose the file I want (once per week the las WeeklyReport_xxxxxxx.xlsx"), and copy the first sheet of it TO my main workbook "LocalReport", each time I do it the copy must be done on a new sheet named MMDDYYYY.
When it will work, I will run filters to be able to pick up only the columns I want.

Thanks for helping me.
LVL 38

Expert Comment

ID: 33778986
Here are some links containing code for different variations on "creating a new workbook with worksheets selected and copied from other workbooks", depending on the data layouts.  Hopefully, you will find that one of the posts provides almost everything you need.  If not, you would have to read through all of the posts and extract what you need from the code provided in these posts.  

LVL 18

Accepted Solution

Curt Lindstrom earned 1500 total points
ID: 33782312
Here is a simple version which will do what you want to do. You can still add a lot of checks to fool proof this version but it will work as is providing you add new reports named as you describe.

Try code in the attached file by pressing the button "Get New Report"

Sub Copy_Reports()

    Dim Wb1 As Workbook
    Dim Wb2 As Workbook
    Dim ws2 As Worksheet
    Dim NewReportSheetName As String
    Dim strFileName As String
    Dim strFolderName As String
    Set Wb1 = ThisWorkbook
    strFileName = Application.GetOpenFilename _
(FileFilter:="Microsoft Excel Files (*.xlsx),*.xlsx,All Files (*.*),*.*")
    Set Wb2 = Workbooks.Open(strFileName)
    Set ws2 = Wb2.Sheets(1)
    strFileName = Left(strFileName, Len(strFileName) - 5)
    NewReportSheetName = Right(strFileName, 8)
    ws2.Copy after:=Wb1.Sheets(Wb1.Sheets.Count)
    Wb1.Sheets(Wb1.Sheets.Count).Name = NewReportSheetName
End Sub

Open in new window


Author Closing Comment

ID: 33900815
I had to make a "mix" code to reach my goal. The solution of epaclm facilitate the fundamental of my beta code.

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question