0

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.

3
  • The show code is for the mentioned worksheet, "Purchase_Orders" and will work only for the worksheet, not for others !! If you want the Macro to run on another Sheet ,, need to replace the Sheet name. Jun 26, 2018 at 7:12
  • Sorry I should also mention that this is within a With which is referencing another sheet. so the .Range("A2:A" & lastRow).PasteSpecial isn't referencing Purchase_Orders. Thanks Jun 26, 2018 at 8:02
  • it's With newPlan.sheets("Requirements") wrong use replace sheet name with Workbook name and use sheets below ! Jun 26, 2018 at 8:24

0

You must log in to answer this question.

Browse other questions tagged .