VBA code to copy contents from newly added worksheets to an already existing worksheets on the same workbook

[EckanPearl]

Hi guys,
I have a macro code that opens up excel files and adds them up to an existing workbook (that is already open) with already 19 worksheets (worksheet names as follows: “result”, “1”, “2”, “3”,.. until “18”).
Now after adding the new worksheets (18 total as well with varying sheet names, and is always changing), I will filter the column AG (criteria =1) fo each newly added worksheet and copy them accordingly to existing worksheets “1” until “18”. However, this is how I want it to go: The filtered contents of 1st newly added worksheet gets copied to sheet “1”, the 2nd newly added worksheets filtered contents get copied to sheet “2”, and so on until the 18th newly added worksheet filtered contents gets copied onto sheet “18”.
Appreciate if you could help me write a code based on this.thanks a lot!

 

[My Aswer Is This]

Hi guys,
I have a macro code that opens up excel files and adds them up to an existing workbook (that is already open) with already 19 worksheets (worksheet names as follows: “result”, “1”, “2”, “3”,.. until “18”).
Now after adding the new worksheets (18 total as well with varying sheet names, and is always changing), I will filter the column AG (criteria =1) fo each newly added worksheet and copy them accordingly to existing worksheets “1” until “18”. However, this is how I want it to go: The filtered contents of 1st newly added worksheet gets copied to sheet “1”, the 2nd newly added worksheets filtered contents get copied to sheet “2”, and so on until the 18th newly added worksheet filtered contents gets copied onto sheet “18”.
Appreciate if you could help me write a code based on this.thanks a lot!

 

[EckanPearl]

HI there ,thanks for taking time to reply. However I could not see the script you provided for some reason.

 

[My Aswer Is This]

I just increased the font size of your original post so I could read it. You used a very small font size. I have no answer at this point

HI there ,thanks for taking time to reply. However I could not see the script you provided for some reason.

 

[EckanPearl]

Hi, I just want to share the answer i got form another forum. With minor modifications on actual filter range, it worked perfectly. Here it is:


Sub sbUpdatedSheets()
Dim i, NumSheets As Integer
Dim lastRow As Long
NumSheets = 18
For i = 1 To NumSheets
'Filter the new source sheet and copy the content
Sheets(NumSheets + 1 + i).Activate
ActiveSheet.Range("$A$1:$CW$1159").AutoFilter Field:=29, Criteria1:="1"
ActiveSheet.Range("$A$1:$CW$1159").Copy
'Find the last free row in destination sheet and paste
Sheets(i + 1).Activate
lastRow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do While Application.CountA(ActiveSheet.Rows(lastRow)) = 0 And lastRow <> 1
lastRow = lastRow - 1
Loop
Range("A" & lastRow).Select
ActiveSheet.Paste

Next

ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("result").Select

End Sub