VBA excel to copy formula from a worksheet and paste to multiple worksheets

1
votes

VBA excel to copy formula from a sheet and paste to an array of worksheets. As an example, I want to copy data from worksheet Data!C4:CX204 and paste to worksheets Test1:Test50. The Worksheets from Test1:Test50 will be right after each other. I was thinking I can name one cell Test1 and another Test 50 and link to those cells in order to make the array more flexible. 

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
2
vbaexcelloopscopy-paste
What VBA code have you tried? – braX
I don't have any experience with VBA. I saw some things on line but the thing that was missing from most of the code was the ability to choose the array of worksheets. Most codes were there to choose ALL sheets. See below as an example. – roadtoenlightenment
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 – roadtoenlightenment

2 Answers

2
votes

Is this what you are looking for? The i = 1 to worksheets count can be changed to what ever you need. The second part of the copy/paste is the destination.

Sub Button4_Click()
Dim I As Integer

' Begin the loop.
For I = 1 To ActiveWorkbook.Worksheets.Count - 1
Worksheets(1).Range("C11:C300").Copy Worksheets("Test" & I).Range("C11:C300")
Next I

End Sub
1
votes

Based on the code you posted, you probably want something like this:

Sub Button4_Click()
Dim src As Range, sel As Range
Dim i as long

Set sel = Selection
Set src = ActiveWorkbook.Sheets("Data").Range("C10")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 to 50
    src.Copy ActiveWorkbook.Sheets("Test" & i).Range("C11:C300")
Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

Application.Goto sel, False

End Sub