0

I copy and paste one sheet to the other, but the amount of data always changes. Whenever I paste, a column at the end of the sheet labeled 'Grand Total' appears but it messes up the data on another sheet so I want to delete it. It deletes from the second code but not the first. I need help figuring out why the first doesn't work.

 Set RidGrTotal = Range("B1:AH3000")
    isum.Sheets("pivot custom").Activate
    For Each cell In RidGrTotal
        If cell.Value = "Grand Total" Then cell.EntireColumn.ClearContents
    Next  

    Set RidGrandTotal = Range("B1:AH3000")
    isum.Sheets("Pivot to MW").Activate
    For Each cell In RidGrandTotal
        If cell.Value = "Grand Total" Then cell.EntireColumn.ClearContents
    Next
  • You can put four spaces before each line, instead of the backticks. It looks nicer and is easier to copy. – wizzwizz4 Jul 12 '18 at 18:43
  • @wizzwizz4 Better yet, highlight the whole block of code and press Ctrl + K on your keyboard :) – dwirony Jul 12 '18 at 18:48
  • 1
    `Set RidGrTotal = Range("B1:AH3000")` this range is only qualified to whatever sheet is *active* at run-time. You loop through that range twice, and the second time you do it will have no effect because it's just repeating what it did in first loop. You need to reassign this for each sheet or use some alternate method to process each sheet. – David Zemens Jul 12 '18 at 18:50

1 Answers1

1

Qualify your ranges properly, and you can avoid relying on Activate and all the problems that arise from implicit qualifications, etc.:

Dim cell as Range
Dim addr = "B1:AH3000"


For Each cell In isum.Sheets("pivot custom").Range(addr)
    If cell.Value = "Grand Total" Then cell.EntireColumn.ClearContents
Next  

For Each cell In isum.Sheets("Pivot to MW").Range(addr)
    If cell.Value = "Grand Total" Then cell.EntireColumn.ClearContents
Next
David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Ah ok, I was not sure before how the activate came into play but that makes sense, thank you. –  Jul 12 '18 at 20:06