Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
I need a piece of code that will cycle through each worksheet and perform a
Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
Sub cycleThruSheets()
Dim ws As Worksheet For Each ws In Worksheets MsgBox ws.name Next ws End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
Just to add to Mike's response:
Sub cycleThruSheets() Dim ws As Worksheet dim RngToCopy as range set rngtocopy = worksheets("somesheetnamehere").range("x99") For Each ws In Worksheets 'avoid the worksheet held the sending cell???? if lcase(rngtocopy.parent.name) = lcase(ws.name) then 'skip it else 'just a sample criteria if ws.range("a1").value 99 then 'do the copy rngtocopy.copy _ destination:=ws.range("b19") end if end if Next ws End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
I am not sure this is what I am looking for. I just want it to cycle
through each worksheet (I will set screen updating to false) and paste data into B19 of each worksheet. "Mike" wrote in message ... Sub cycleThruSheets() Dim ws As Worksheet For Each ws In Worksheets MsgBox ws.name Next ws End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
Sub Macro1()
' ' Macro1 Macro ' Macro recorded 12/1/2008 by Jmay ' Dim wb1 As Workbook Dim wb2 As Workbook Set wb1 = Workbooks("Book3") 'source wb Set wb2 = Workbooks("Book2") 'destination wb With wb2 For i = 1 To .Worksheets.Count .Worksheets(i).Range("B19").Value = wb1.Sheets("Sheet1").Range("C7").Value Next End With End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
Both Mike's and Dave's suggestions do cycle through the worksheets using the
For...Next loop. All you have to do is put the code in between that does the pasting. Dave even gave you some extra sample code to get you started with that. If you are a beginner in code writing, you should be specific and complete in what you are trying to do. It is very difficult to sit hundreds of miles away and guess at what you want to accomplish. "Patrick C. Simonds" wrote: I am not sure this is what I am looking for. I just want it to cycle through each worksheet (I will set screen updating to false) and paste data into B19 of each worksheet. "Mike" wrote in message ... Sub cycleThruSheets() Dim ws As Worksheet For Each ws In Worksheets MsgBox ws.name Next ws End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Cycle through all workshhets
Thank you you are right. I thought by not going into to much detail I could
get something I could work with (adapt) without consuming to much of everyone's time. Clearly that was not the case an I am very much out of my league. What I am trying to do is create a template of our vacation calendar which when the year is changed would insert our allowed vacations (which are represented by graphics shapes I have created). This workbook has 105 worksheets. Each worksheet is made up of either Monday - Wednesday or Thursday - Sunday. The code below (which was provided by Peter T) goes to the worksheet called Holidays and copies the shape (in this case the shape representing New Years). What I now need to do is have my code look through each of the worksheets (excluding Holidays) at range("B56") and if it is equal to "New Years" paste the shape into cell B19. If B56 does not equal "New Years" then it will check cell E56 (with the shape going into cell E19) then cell H56 (with the shape going into cell H19). Those test I feel I can write with out any problems. But I can not get it to cycle through each of the 104 Worksheets. I will also create a routine for the other 5 holidays through the year and am assuming that they can be easily modeled on the New Years routine. Sub NewYearsInsert() Dim lt As Single, tp As Single Dim rTL As Range Dim shp As Shape Dim rng As Range Set shp = ActiveWorkbook.Worksheets("Holidays").Shapes("New Years Large") With shp Set rTL = .TopLeftCell lt = .Left - rTL.Left tp = .Top - rTL.Top .Copy End With End Sub "JLGWhiz" wrote in message ... Both Mike's and Dave's suggestions do cycle through the worksheets using the For...Next loop. All you have to do is put the code in between that does the pasting. Dave even gave you some extra sample code to get you started with that. If you are a beginner in code writing, you should be specific and complete in what you are trying to do. It is very difficult to sit hundreds of miles away and guess at what you want to accomplish. "Patrick C. Simonds" wrote: I am not sure this is what I am looking for. I just want it to cycle through each worksheet (I will set screen updating to false) and paste data into B19 of each worksheet. "Mike" wrote in message ... Sub cycleThruSheets() Dim ws As Worksheet For Each ws In Worksheets MsgBox ws.name Next ws End Sub "Patrick C. Simonds" wrote: I need a piece of code that will cycle through each worksheet and perform a Paste into cell B19 of each worksheet. I do not want to select all worksheets but cycle through them. At some point I will be modifying the code to where if a certain condition is not met, the paste will not take place. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a Macro for Sorting workshhets | Excel Programming | |||
How do I keep result from 1 iteration cycle to use in next cycle? | Excel Discussion (Misc queries) | |||
how can i merge two workshhets in to one? | New Users to Excel | |||
sum between workshhets | Excel Discussion (Misc queries) | |||
merge/combine workshhets | Excel Discussion (Misc queries) |