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
Improve this question
3
  • You can put four spaces before each line, instead of the backticks. It looks nicer and is easier to copy. Commented Jul 12, 2018 at 18:43
  • @wizzwizz4 Better yet, highlight the whole block of code and press Ctrl + K on your keyboard :) Commented Jul 12, 2018 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. Commented Jul 12, 2018 at 18:50

1 Answer 1

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Ah ok, I was not sure before how the activate came into play but that makes sense, thank you.