Copy and paste data from multiple worksheets in a master sheet

[Krissnyc]

Hello everyone,

I just joined the forum, and this is my first post. I am totally naïve to running macros and need your help to automate the consolidation of multiple worksheets. I have around ten worksheets - columns A to M are fixed in all the spreadsheet and rows keep changing as and when the data is inputted. The first seven rows include the title and other information. I have created a master sheet at the beginning with titles already copied and would like to get the data from all worksheets starting from row 7 and columns A to M.

I tried quite a few codes, including setting current region, etc., but the result looks awful as it also copies titles and creates extra spaces in between. I just want to see the data continuously flowing without titles and automatically include new rows. I would greatly appreciate your input. Thank you!

 

[jolivanes]

Remember that this code is tailored for the 7 rows above your data to be copied and pasted.
I think that the owner of this site has quite a few good books out.
I have John Walkenbach's Power Programming with VBA but most of it you'll pick up on these forums.
If you see something that might be of interest, read/copy the info/code and make your own file to try it.

 

[Krissnyc]

Great, thanks Jolivanes for taking time to share your knowledge. One question, If I wanted to run this macro from any sheet but update a specific sheet named "Master Data" would this be the syntax for sheet name - sheet("Master Data").Name in the 3rd row?

 

[jolivanes]

If all your Columns from A to M are the same lengths, I would use this. (from any sheet)

Sub Same_Length_Columns()
    Dim Master As Worksheet, sh As Worksheet
    Set Master = Sheets("Master Data")
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> Master.Name Then
            With sh
                .Range("A8:M" & .Cells(.Rows.Count, "A").End(xlUp).Row).Copy Master.Cells(Rows.Count, "A").End(xlUp).Offset(1)
            End With
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

If the Columns from Column A to Column M could be of different lengths, this would do. (from any sheet)
However, if you don't need it I would not use it because later on if you need to change things you
have to remember why lr1 is 8 and also to what you need to change the lr1 = lr1 + lr - 7.
I gave you this because you did not tell me that all the Columns were the same length or not.
Most of the time however, I think Columns are the same length.

Sub Different_Length_Columns()
    Dim Master As Worksheet, sh As Worksheet, lr As Long, lr1 As Long
    Set Master = Sheets("Master Data")
    lr1 = 8
    Application.ScreenUpdating = False
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> Master.Name Then
            With sh
                lr = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
                .Range("A8:M" & lr).Copy Master.Range("A" & lr1)
            End With
        lr1 = lr1 + lr - 7
        End If
    Next sh
    Application.ScreenUpdating = True
End Sub

Good luck.

I see now that I did not really answer your question in your last post but I am sure you can deduct.

 

[Krissnyc]

Thanks again, Jolivanes. The length is same in all the columns - beginning from row 7 and starting from A to M. If columns get extended, I simply need to change that, right? so with the row length from 7 to 9 then lrl = 9 and later "lr-8". Thanks for clarifying the syntax for worksheet name. This has been very helpful and motivating to learn VBA

 

[jolivanes]

I would not call it "If Columns Get Extended" but more like "If the header rows get extended". You probably mean that, don't you!
Yes, just adjust both. As long as the amount of rows are the same in all sheets. That's why the first code would be easier to maintain.
Good luck and you know where to go to if you have any problems. MrExcel is the place!

 

[Krissnyc]

Thanks a lot, Jolivanes!