Macro to Consolidate Data

[howard]

I have written code to consolidate data from sheet1 up to one sheet before the last sheet and to paste these in sheet "Consolidated Data"

The data is copied and pasted from the respective sheets

I need my macro amended so that the data is pasted from row 2 onwards on sheet "Consolidated Data"


It would be appreciated if someone could amend my code accordingly

 Sub Consolidated ()

Dim LR As Long

With Sheets("Consolidated Data")
LR = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:M").ClearContents
 ShtCount = ActiveWorkbook.Sheets.Count

 For i = 1 To ShtCount - 1

 Worksheets(i).Activate
 LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

 Range("A2:P" & LastRow).Select

 Selection.Copy
 Sheets("Group Expenses").Activate

 LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Select

 'Required after first paste to shift active cell down one
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
 Loop


Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Range("A:M").EntireColumn.AutoFit

 Next i
End With

End Sub

 

[Fluff]

Apart from the fact that code cannot work, which sheet are you trying to copy to?
You say one thing, but your code is doing something totally different.

 

[howard]

My apologies

Sheets("Group Expenses").Activate should have been Sheets("Consolidated Data")

I am trying to copy the data to Sheets("Consolidated Data")

 

[Fluff]

How about

Sub howard()
   Dim i As Long
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Consolidated Data")
   Ws.Range("A2:P" & Ws.Range("A" & Rows.Count).End(xlUp).Row).ClearContents

   For i = 1 To ActiveWorkbook.Sheets.Count - 1
      With Sheets(i)
         .Range("A2:P" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
         Ws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      End With
   Next i
   Ws.Range("A:P").EntireColumn.AutoFit
End Sub

 

[howard]

Hi Fluff

Thanks for the help. Code works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.