Loop - Tabs
 

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

Loop - Tabs
 

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.

--
Regards,
Tom Ogilvy

"JohnUK" wrote:

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

Loop - Tabs
 

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"JohnUK" wrote:

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

Loop - Tabs
 

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.
--


Gary


"JohnUK" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"JohnUK" wrote:

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

Loop - Tabs
 

Superb
Many thanks I really appriciate the help


"Gary Keramidas" wrote:

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.
--


Gary


"JohnUK" wrote in message
...
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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"JohnUK" wrote:

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

Loop - Tabs
 

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.

--
Regards,
Tom Ogilvy



"JohnUK" wrote:

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"JohnUK" wrote:

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