Cycle through all workshhets

[Patrick C. Simonds]

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.

 

[Mike]

Sub cycleThruSheets()
Dim ws As Worksheet

For Each ws In Worksheets
MsgBox ws.name
Next ws
End Sub

 

[Dave Peterson]

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]

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.

 

[JMay]

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

 

[JLGWhiz]

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]

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