Multiple worksheets with same form layout - into one list of rows worksheet.

[natheplas]

Hi All,

Is there anyway with excel to compile loads of data from multiple work sheets into one worksheet?

I have loads of worksheets that have the exact same layout on each one.

I then want a way to pull each work sheet to a new row on one worksheet.

Can anyone help me with this please?

Nathan

 

[natheplas]

Hi All,

I've figured out to use some different VBA codes to compile all the data together.

However, it pastes it into on worksheet as a mess.

Is there anyway to map what cells go in which column and make sure each worksheet is a new row?

I really hope there's a way to do this!

Natheplas

 

[Fluff]

How about something like this

Sub AmalgamateSheets()

    Dim Ws As Worksheet
    
    For Each Ws In Worksheets
        If Not Ws.Name = "[COLOR=#0000ff]Data[/COLOR]" Then
            Ws.UsedRange.Offset(1).Copy Sheets("[COLOR=#0000ff]Data[/COLOR]").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next Ws
    
End Sub

This will copy info to a sheet called Data, change the names in blue to suit

 

[natheplas]

Hi Fluff,

Thank you for coming back to me. I'm fairly new to VBA on excel.

How does this code work. I currently have a load of workbooks in folders. 2017, then month, then each month of workbooks.

I ideally want the VBA to merge all of 2017 workbooks together, but somehow organise where the data goes on to the 'master' spreadsheet.

Name, Company, etc

column a, column b etc

Any ideas? Might have to hold my hand a little bit with this one - Not sure how to put together something like that.

 

[Fluff]

Originally you said

Is there anyway with excel to compile loads of data from multiple work sheets into one worksheet?

Now you are saying

I ideally want the VBA to merge all of 2017 workbooks together

Additionally you originally said

I have loads of worksheets that have the exact same layout on each one.

but you now say

but somehow organise where the data goes on to the 'master' spreadsheet

which makes me think that your original statement was wrong.

Could you please let us know exactly what you want. along with samples of what your data looks like & how you need it to look.
Assuming that you want want to copy from multiple workbooks, we'll need the following info
1) what are the folder names to look in
2) do you want to copy from every workbook in those folders
3) if there is more than 1 sheet in each workbook, what sheets do you need copying
4) does the master sheet exist?
5) does everything get copied to 1 sheet within the master

 

[natheplas]

Hi Fluff,

I put together something that works now, it's not great, but it does the job. Sorry for any confusion and thanks for coming back to me.

Natheplas

 

[Fluff]

Glad you got it sorted & thanks for letting us know