VB Excel Copy all workbooks in one with separate sheets

Posted on 2010-09-23
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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 500 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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Outlook Free & Paid Tools
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

820 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