Copy data from multiple worksheets (loop) to workbook and save as new workbook

**czap1** · 01-10-2024, 12:34 PM

Hello,

I would like some help to write a macro to do the following:

There are 2 workbooks. 'Master' and 'Data'

The Master workbook contains a sheet with formulas in columns D:F
In the Data workbook there are multiple worksheets with different names and with the data in columns A:C

The macro should:

1. Open both workbooks
2. Copy data from data!.Sheet("aa").range(A:C) and paste into Master!Sheet ("Summary")
3. In Master!Sheet ("Summary"). Range.("D:F").Select (formula range). Fill down to lastrow in columnA. OR delete rows to match lastrow in column A. (The data will not always have the same number of rows)
4. Save the Master workbook as new workbook with the name of the data worksheet. In this case: aa.xlsm
5. Reopen the Master workbook and repeat (loop)through all the sheets in the data workbook.

So, if there are 10 worksheets in the data workbook, 10 new workbooks will be created with corresponding names.

Many thanks,

CC

**Marc L** · 01-10-2024, 01:29 PM

Hello,

follow the top page yellow banner in order to share your workbooks including accordingly at least one exact expected result workbook …

**czap1** · 01-10-2024, 02:47 PM

I have attached 3 files
1.The master workbook where the macro should be
2. The data workbook
3. An example of expected result

Data.xlsm 2000.xlsx Master.xlsm

I am not sure whether I have inserted the attachmentc correctly

**Marc L** · 01-10-2024, 05:58 PM

Yes but it does not match with your initial post :

Originally Posted by czap1

The Master workbook contains a sheet with formulas in columns D:F
In the Data workbook there are multiple worksheets with different names and with the data in columns A:C

So what is wrong : the initial post or the attachment ?! …

**czap1** · 01-10-2024, 06:12 PM

Yes, of course, you are correct. In my initial post I just described the problem out of my head. Then I created some example workbooks and the columns are different. The correct version is the workbooks. So the data columns are columns A:F , and the formula columns are H:K.
Strange that you have D:F in the master workbook.

Apologies for this.

I have resent both workbooks again as the correct version.

P:S the macro could be placed in a separate workbook if necessary.

Data.xlsm Master.xlsm

**Marc L** · 01-10-2024, 06:28 PM

The VBA procedure could be located within Master workbook - whatever - but
does Data workbook - if closed - located within the same folder as Master ?

**czap1** · 01-11-2024, 03:25 AM

Hello Marc,

all workbooks (Master, Data and newly created ones) can be placed in the same folder. I can always change that later if necessary.

Czap

**Marc L** · 01-11-2024, 12:26 PM

According to your small attachment directions to follow :

Data & Master workbooks must be located within the same folder.
Only Master workbook - the VBA procedure container - should be opened,
one more workbook opened then the procedure just produces a beep and bye …
Master workbook just needs rows #1 to 3 … Rows #4 until the end are totally useless, to be deleted !
Paste this starter VBA demonstration only to Master.xlsm ThisWorkbook module :

PHP Code:


Sub Demo1() 
  Const D = "Data.xlsm" 
    Dim R&, Sh As Worksheet, Ws As Worksheet 
        If Dir(Path & "\" & D) = "" Or Workbooks.Count > 1 Then Beep: Exit Sub 
   With Application 
       .DisplayAlerts = False 
       .ScreenUpdating = False 
        Sheets(1).Copy 
        Set Sh = ActiveWorkbook.Sheets(1) 
    For Each Ws In Workbooks.Open(Path & "\" & D, 0).Worksheets 
        R = Sh.UsedRange.Rows.Count 
   With Ws.[A1].CurrentRegion.Rows 
       .Copy Sh.[A1] 
        If .Count < R Then Sh.UsedRange.Rows(.Count + 1 & ":" & R).Clear Else _ 
        If .Count > R Then Sh.Rows(R & ":" & .Count).Columns("H:K").FillDown 
   End With 
        Sh.Parent.SaveAs Path & "\" & Ws.Name, 50 
    Next 
       .Speech.Speak "Done!", True 
        Workbooks(D).Close 
        Sh.Parent.Close 
       .DisplayAlerts = True 
       .ScreenUpdating = True 
   End With 
        Set Sh = Nothing 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**czap1** · 01-11-2024, 02:46 PM

Hello Marc,

thanks for this. We are nearly there. First, I defined the Path name and then it worked.

Dim Path As String
Path = "C:\Example"

It copies, pastes and creates the new workbooks.All good.
What it does not do is fill down columns H:K

Can you check that please?

Czap

**Marc L** · 01-11-2024, 03:06 PM

As it well works on my side as it is with your attachment
so you must not define Path if you just well read & follow the directions
as Path is very not a variable but a workbook property ‼

What did not you understand in my previous post directions ?!

**czap1** · 01-11-2024, 04:23 PM

Well, I think I did follow your instructions.

The 2 files are situated in a folder named 'Exchange' on my C:drive

I opened the Master file and inserted Module 1 in Modules. I then copied in your code.

If I run the macro using F5 nothing happens apart from a sound.
If I use F8, then it continues on to the 3rd line to Exit Sub. It does not follow on to With Application etc....

If I define the path, then it works. As said, it copies and creates new files perfectly. But, it does not fill down columns H:K.

I also deleted rows 4 downwards in the master file.

**Marc L** · 01-11-2024, 06:43 PM

Originally Posted by czap1

I opened the Master file and inserted Module 1 in Modules..

So the bad as obviously very not the specific direction about the right place for the VBA procedure ‼
So just try to well read & follow the direction and ask for if you do not understand it.
As on my side my demonstration - as it is - works as expected …

**czap1** · 01-12-2024, 03:35 AM

Marc, sorry about that. I always thought that macros had to be inserted into modules. I now inserted it into 'This Workbook', as you originally instructed, and it works. (Copies, pastes columns A:F, creates new named workbooks)

Unfortunately, it still does not fill down columns H:K. Is there another instruction that I have not followed correctly? Why do you use the # before the row in instructions line 3? In the master file, I have simply deleted all rows from row 4 downwards. Is that correct? As said, the macro inserts the data into columns A:F correctly.

**Marc L** · 01-12-2024, 05:46 AM

Did you test with the same attachment from this thread ?
As columns H:K are filled down on my side even in your attachment only columns H:I have formulas …

**Marc L** · 01-12-2024, 05:51 AM

Maybe 'cause I wrote a code for smart worksheet (fails with dumb one) so
maybe on your side row number (#) 4 until the end are still not deleted
so select first entire row #4 until the end (#1048576)
and once all the block is selected then right click and choose delete …

**czap1** · 01-12-2024, 07:16 AM

SOLVED

Marc,

that worked! So all good. Thank you so much for your time and patience!

I could not find the bottom left star icon, but there is an option to rate this thread, which I have done.

Best regards

Czap

**czap1** · 01-12-2024, 08:12 AM

SOLVED

Marc,

that worked! So all good. Thank you so much for your time and patience!

I could not find the bottom left star icon, but there is an option to rate this thread, which I have done.

Best regards

Czap

Copy data from multiple worksheets (loop) to workbook and save as new workbook

LinkBack

Thread Tools

Rate This Thread

Display

Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Try this !

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Re: Copy data from multiple worksheets (loop) to workbook and save as new workbook

Thread Information

Users Browsing this Thread

Similar Threads

Filtered Data Loop, Copy and Paste into New Workbook, delete first column and save

[SOLVED] VBA code to create a workbook and copy or move worksheets in to workbook through loop

Copy data from one workbook to a master workbook w/ multiple worksheets by a Submit button

[SOLVED] Loop through worksheets to copy data to new workbook

Save Copy of Macro Workbook as new Workbook with only Worksheets and values, Same Location

Need a loop script to copy from one 'data' workbook' to a template, make some edits, save.

loop through a column on a workbook copying data on each row to another workbook, then copy data back to the original workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions