Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
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.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

609 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