Solved

VB Excel Copy all workbooks in one with separate sheets

Posted on 2010-09-23
10
407 Views
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.
0
Comment
Question by:Lommag
[X]
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
10 Comments
 
LVL 24

Expert Comment

by:broomee9
ID: 33744836
0
 

Author Comment

by:Lommag
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
0
 

Author Comment

by:Lommag
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:broomee9
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.

Book1.xls
0
 

Author Comment

by:Lommag
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
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.  

http://www.computing.net/answers/office/vba-to-open-many-workbooks/11965.html
http://www.computing.net/answers/office/excel-macro-to-create-new-worksheet/9820.html
http://www.computing.net/answers/office/excel-macro-to-create-sheets/10810.html
http://www.computing.net/answers/office/need-excel-macro-to-open-files/10432.html
0
 
LVL 18

Accepted Solution

by:
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"

Regards,
Curt
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

Local-Reports.xlsm
0
 

Author Closing Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
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…
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…

724 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