I have a macro which autofilters a range on one sheet and then copies certain columns to another sheet.
The macro runs fine from all sheets in the workbook, apart from the page that I actually want to run it from using a button.
The code looks like this
With newPlan.sheets("Requirements")
newPlan.Sheets("Purchase_Orders").Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).Copy
.Range("H4").PasteSpecial xlPasteValues
newPlan.Sheets("Purchase_Orders").Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible).Copy
.Range("I4").PasteSpecial xlPasteValues
newPlan.Sheets("Purchase_Orders").Range("J2:J" & lastRow).SpecialCells(xlCellTypeVisible).Copy
.Range("J4").PasteSpecial xlPasteValues
End With
//Etc
When run from the sheet that doesn't work, it will copy and paste the first range fine and then give an error that "No cells were found" 1004.
I can comment out each line and the next one will copy fine, so there are no typos. It can only copy the first instance for some reason.
Does anyone know what might be causing this? There is nothing special about the sheet I am trying to run it from, other than it only contains different buttons to do different tasks.