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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails


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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Outlook Free & Paid Tools
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now