VBA - copy same range of cells from every worksheet in workbook into list in another worksheet

[>>SleeB<<]

I have a workbook with a number of worksheets (the number may change over time). Within each worksheet I want to copy the range A17:N154 and paste it into one master worksheet.

In other words, taking this same range from each worksheet and compiling all of the data into one long "list" in another worksheet. It would cover columns A:N but would vary in row length based on how many sheets of data need to be copied into it.

Can anyone help me write some code that will automate this process for me?

Thanks!

 

[MARK858]

Try

Sub CopyIt()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A17:N154").Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Obviously change sheet "Master" to suit

 

[dermie_72]

do you have any code that you've created so far?
If not. I would suggest, Recording a macro of you opening the master file, then opening the copy file, so we can see the names, then copying from the first sheet to the master, again so the names can be determined.
Post that code, so we can go over it with you.

 

[>>SleeB<<]

This is obviously really rudimentary:



Sub Macro3()
'
' Macro3 Macro
'


'
Application.Goto Reference:="Thailand!A20:N154"
Selection.Copy
Application.Goto Reference:="Master!A20"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="Sentra!A20:N154"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="Master!A155"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.Goto Reference:="'CJ E& M'!A20:N154"
Application.CutCopyMode = False
Selection.Copy
Application.Goto Reference:="Master!A290"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub




What I am doing here is going to each sheet manually. I guess I would need something like MARK858 wrote above: if ws <> "Master" then copy and cycle through each sheet.

The problem lies in that I need the copied data to be pasted below the data already present and I can't figure out how to go to the bottom of the data already there so I'm not pasting on top of it. I think MARK858's code is copying over the same rows each time it moves to the next sheet?

The number of rows stays constant though (135 rows), so perhaps something that moves the paste area down 135 rows every time (e.g. A20, A155, A290, A425, etc. ) and then cycles through every worksheet.

Thanks again for your help

 

[MARK858]

The problem lies in that I need the copied data to be pasted below the data already present and I can't figure out how to go to the bottom of the data already there so I'm not pasting on top of it. I think MARK858's code is copying over the same rows each time it moves to the next sheet?

NO it pastes to the next row of column A in the sheet called "Master". Try running it on a copy of your data

 

[>>SleeB<<]

Thank you, MARK858

Can you clarify what you mean by copy? I have the worksheets copied into a new workbook for testing....

 

[MARK858]

I mean another workbook set up the same as your actual workbook. You should always test vba on test data as you can't undo the action if it gives undesired results. Basically what you have done as long as one sheet is named Master

 

[warrenzm]

Newbie here...but grateful to prior posters for solving a problem I had. I don't know VBA, but was able to post the Mark858 code and got it to work perfectly. My question is how would I alter that code so that the result is "Paste Values", not just paste. I have a similar problem, but the cells in each worksheet are formulas with relative references and regular pasting doesn't work.

Much thanks in advance.
wzm

 

[MARK858]

I will post something if no-one else has when I am online tonight but please don't pm people just because you have posted to a thread. Most of the regular posters are subscribed to threads they have taken part in and will reply if they choose.

 

[MARK858]

Sub CopyIt()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A17:N154").Copy
            Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End If
    Next
    Application.ScreenUpdating = True
End Sub