Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, sh4 As Worksheet, sh5 As Worksheet, sh6 As Worksheet, sh7 As Worksheet, sh8 As Worksheet, sh9 As Worksheet, sh10 As Worksheet
Dim wb As Workbook, wb2 As Workbook
Dim CopyRange As Range
Dim RangeRev As Range
Dim lastrow As Long, lastrow2 As Long, lastrow3 As Long
Dim fill As Range, fill2 As Range
'wb=Current Month Template
Set wb = ThisWorkbook
Set sh1 = wb.Sheets("Query")
Set sh2 = wb.Sheets("Credits")
Set sh3 = wb.Sheets("Duplicate")
Set sh4 = wb.Sheets("Rev Rvsd")
Set sh5 = wb.Sheets("Rebills")
Set sh6 = wb.Sheets("Pivot")
Set sh7 = wb.Sheets("Work")
Set sh8 = wb.Sheets("Removed")
Set sh9 = wb.Sheets("Validation")
Set sh10 = wb.Sheets("Excluded")
lastrow = sh2.Cells(Rows.Count, "B").End(xlUp).Row
Set fill = sh2.Range("AG3:AG" & lastrow)
lastrow2 = sh5.Cells(Rows.Count, "D").End(xlUp).Row
Set fill2 = sh5.Range("AI2:AI" & lastrow2)
lastrow3 = sh10.Cells(Rows.Count, "A").End(xlUp).Row
here is where i defined the sheets, then below is where the copy function is on one of the sh.
I'd like the code to look at another sheet (rebills) and essentially do the same thing we did on the (credits) but since there will already be info on sh.10 - i need it to paste the last row after the info from the credit's tab has been pasted. I dont have an issue with the credit's one, it's the rebills one that i have issues with the pasting line. (in purple)
credits
'Filter by excluded products and cut and paste into the excluded tab credits
sh2.Range("A2:BA65000").AutoFilter Field:=44, Criteria1:=Array( _
"USRGRP", "FORMS", "KIA", "FRT", "UPS", "TAX", "BMX", "DOCSCN", "ENCRYP", "FORMS", "FRT", "IBMSS", "IDSCAN", "INVSTR", "ISERES", "NCIPHR", "OS400", "OTHER", "PRNTRS", "PSERES", "PWRSYS", "RKUNTS", "SCANER", "SECRTY", "SERVER", "SFTWR", "SLVPK", "SORTRS", "SPTLNE", "STORAG", "SWMA", "SWSUB", "UNISYS", "UPS", "XSERES"), Operator:=xlFilterValues
sh2.Range("A3:AU65000").SpecialCells(xlCellTypeVisible).Copy
With sh10
sh10.Cells(2, 1).PasteSpecial xlPasteAll
End With
sh2.Range("A3:AU65000").Offset(1, 0).EntireRow.Delete
sh2.Range("A2:BA65000").AutoFilter Field:=44
rebills
'Filter by excluded products and cut and paste into the excluded tab rebills
sh5.Range("A2:AW65000").AutoFilter Field:=46, Criteria1:=Array( _
"USRGRP", "FORMS", "KIA", "FRT", "UPS", "TAX", "BMX", "DOCSCN", "ENCRYP", "FORMS", "FRT", "IBMSS", "IDSCAN", "INVSTR", "ISERES", "NCIPHR", "OS400", "OTHER", "PRNTRS", "PSERES", "PWRSYS", "RKUNTS", "SCANER", "SECRTY", "SERVER", "SFTWR", "SLVPK", "SORTRS", "SPTLNE", "STORAG", "SWMA", "SWSUB", "UNISYS", "UPS", "XSERES"), Operator:=xlFilterValues
sh5.Range("C2:AW65000").SpecialCells(xlCellTypeVisible).Copy
sh10.Range("A" & lastrow3).selection.PasteSpecial xlPasteAll
sh5.Range("C2:AW65000").Offset(1, 0).EntireRow.Delete
sh5.Range("A2:BA65000").AutoFilter Field:=46
