Loop - Tabs

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I am in need of some help.
I want to copy and paste from one worksheet to 31 tabs. I have got as far as
getting a loop to change the data on the main page (below) but what I need
now is to incorporate into the code so that the main page changes data,
copies to sheet 1(They are named as people) goes back to main page,
recalculates for next persons data, copy then paste into the next worksheet
along and so on until it has populated all sheets (31 of them)

Sub Copy_Paste()
Dim num As Long
For num = 1 To 32
With ActiveSheet
Application.Wait Now + TimeValue("00:00:01") 'This is to allow the
spreadsheet time to calculate
Range("X2").Value = .Range("X2").Value + 1 'Formula works off this
cell
.Range("X4").Value = num
.Calculate
End With

Next num
End Sub
 
Assumes the first sheet is the Main sheet
Sub Copy_Paste()
Dim num As Long
For num = 1 To 31
With Worksheets(1)
.Range("X2").Value = .Range("X2").Value + 1
.Range("X4").Value = num
.Calculate
doevents
.cells.copy
End with
worksheets(num +1).PasteSpecial xlValues
Next num
End Sub

Calculate isn't asynchronous, so unless you are having problems, you
shouldn't have to "wait". The code will "wait" automaticaly.
 
Hi Tom,
Thank you again for your help.
I have tried to run the code but it stops on the:
Worksheets(num + 1).PasteSpecial xlValues
 
i think. that line just needs a range to copy to.
for example:
Worksheets(num + 1).Range("A1").PasteSpecial xlValues

see how it goes after changing it.
 
Superb
Many thanks I really appriciate the help


Gary Keramidas said:
i think. that line just needs a range to copy to.
for example:
Worksheets(num + 1).Range("A1").PasteSpecial xlValues

see how it goes after changing it.
Click to expand...
 
I had meant it to be:

Sub Copy_Paste()
Dim num As Long
For num = 1 To 31
With Worksheets(1)
.Range("X2").Value = .Range("X2").Value + 1
.Range("X4").Value = num
.Calculate
doevents
.cells.copy
End with
worksheets(num +1).Cells.PasteSpecial xlValues
Next num
End Sub

But adding Range("A1") works as well.
 
Back
Top