Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
tabs are missing even though 'tools-options-view-sheet tabs' ok? | Excel Worksheet Functions | |||
hide tabs from view then lock tabs? | Excel Discussion (Misc queries) | |||
loop through tabs | Excel Programming | |||
Run out of resources .... loop to new tabs | Excel Programming | |||
Loop to new tabs | Excel Programming |