CODEWITHSUNDEEP
excelvba
jules325
jules325

Reputation: 179

Simple Copy/Paste Loop not working on each worksheet

I'm trying to loop through all the worksheets in my workbook. On each sheet, I'd like to copy a value from one cell, and paste the value on a different cell in that worksheet, and then continue on through the workbook. The code I've written loops through all the worksheets but only performs the copy paste on the sheet its on when the macro is run...

Sub CleanUp()

Dim ws as Worksheet

For Each ws in ThisWorkbook.Sheets
    Range("BB2").Copy Range("A1")
Next ws

End Sub

In actuality, I want to run a more complicated series of alterations, but I can't even get this to work right.

Upvotes: 0

Views: 1107

Answers (2)

user8753746
user8753746

Reputation:

An alternative solution that could run faster:

Sub CleanUp()

Dim ws as Worksheet

For Each ws in ThisWorkbook.Sheets
    ws.Range("A1").Value = ws.Range("BB2").Value
Next ws

End Sub

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152450

append the worksheet to each range:

Sub CleanUp()

Dim ws as Worksheet

For Each ws in ThisWorkbook.Sheets
    ws.Range("BB2").Copy ws.Range("A1")
Next ws

End Sub

Upvotes: 1

Related Questions