Copy and paste on multiple sheets in Excel using VBA

Question

I am using the following code to loop through all my worksheets copying and pasting the same values on all.

The trouble is my code only applies the changes to the Active sheet.

Sub Button4_Click()

Dim WS_Count As Integer
         Dim I As Integer
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

    Range("C10").Select
    Selection.Copy
    Range("C11:C300").Select
    ActiveSheet.Paste
    Range("C10").Select

         Next I


End Sub

How can I configure my code so that it is applied to all sheets in the loop?

Thanks

SierraOscar · Accepted Answer · 2014-12-27 23:36:52Z

1

No need to select anything, or use a counter - just loop through the worksheets using a worksheet variable and For Each loop. Also no need to select anything or even use the Copy/Paste command in this scenario - you can assign the value directly. See below:

Sub Button4_Click()

Dim wsVar As Worksheet

For Each wsVar In ThisWorkbook.Sheets
   With wsVar
      .Range("C11:C300").Value = .Range("C10").Value
   End With
Next wsVar

End Sub

answered Dec 27, 2014 at 23:36

SierraOscar

17.7k6 gold badges44 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NickSlash · Accepted Answer · 2014-12-27 23:42:22Z

0

Edit: I didn't/dont know exactly what you are trying to do so its hard to write an example.

This code should, copy the value in Sheet1!C10 into the range C11:C300 on every sheet in your workbook. (including Sheet 1 itself). I've not tested it though.

Sub Button4_Click()

Dim WS_Count As Integer
Dim I As Integer
WS_Count = ActiveWorkbook.Worksheets.Count

Dim Source As Range

Set Source = ThisWorkbook.Worksheets(1).Range("C10")

' Begin the loop.
For I = 1 To WS_Count

    ThisWorkbook.worksheets(i).Select ' just select the sheet
    Source.Copy    
    Range("C11:C300").Select
    ActiveSheet.Paste

Next I

End Sub

Selecting sheets and cells like that does work fine, but using sheet objects can be easier.

eg:

Dim A_Sheet As Worksheet: Set A_Sheet = ThisWorkbook.Worksheets("Sheet1")
A_Sheet.Cells(1,1).Value = "Example" ' set Sheet1!A1 to "Example"
A_Sheet1.Range("A1").Value = "Example" ' another way of doing it

Its a little easier as you can access data on any sheet without selecting it first.

edited Dec 27, 2014 at 23:42

answered Dec 27, 2014 at 23:21

NickSlash

5,1954 gold badges25 silver badges41 bronze badges

2 Comments

user3580480 Over a year ago

Thanks, but that doesn't help with my issue. My code does not work, it applies all the changes to the Active sheet. I am trying to figure out how to do the copy and paste for every sheet in my loop.

SierraOscar Over a year ago

You shouldn't use 'Sheet1' as a variable as this is also a codename used to identify a sheet in VBA. This can cause confusion in debugging code, particularly as 'Sheet1' the object, and "Sheet1" the worksheet with that particular name may not be the same sheet in VBA and so 'Sheet1' will no longer refer to the Sheet with an index of '1'

Collectives™ on Stack Overflow

Copy and paste on multiple sheets in Excel using VBA

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related