Copy range from multiple sheets into one sheet column to column

Probably there's a lot of same questions regarding this but this is my first day with VBA and I'm unsure of how it works.

I recorded a macro of what I'm trying to do, I want it to apply to all sheets in the workbook.

I want to copy a few ranges for example M6:M11 and paste it into the Mastersheet, and repeat the process but pasting on the next column instead of the row below it.

I tried using this but this makes it paste in the same column

Sub MM1()
Dim ws As Worksheet, lr As Long
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each ws In Worksheets
If ws.Name <> "Master" Then
ws.Range("A7:K16").Copy Sheets("Master").Range("A" & lr)
lr = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
Next ws
End Sub



This is the recorded macro of what I'm trying to do.

Sub Macro1()
'
' Macro1 Macro
'

'
Sheets("31 Dec").Select
Range("M6:M11").Select
Selection.Copy
Sheets("Master").Select
ActiveSheet.Paste
Range("B1").Select
Sheets("1 Jan").Select
Range("M6:M11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Master").Select
ActiveSheet.Paste
Range("C1").Select
Sheets("2 Jan ").Select
Range("M6:M11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Master").Select
ActiveSheet.Paste
Range("D1").Select
End Sub

Try

Please Login or Register  to view this content.

Kind regards
Leo

Thanks a lot Leo, This works perfectly!!
Apparently I had an issue with this part, the pasting to master sheet.

ws.Range("M6:M11").Copy Sheets("Master").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)

Thanks again!