Charles Chickering
12/13/2006 8:35:00 PM
Oops forgot to read your entire post. Sorry!
Sub CopyToNumberWorksheet()
Dim ws As Worksheet
Dim rEndI As Range
For Each ws In ActiveWorkbook.Worksheets
If UCase(Left(ws.Name, 5)) = "SHEET" Then
If IsNumeric(Right(ws.Name, Len(ws.Name) - 5)) Then
Set rEndI = ws.Range("I" & ws.Rows.Count).End(xlUp)
ws.Range("J3") = ws.Range("I3")
ws.Range(ws.Range("J4"),rEndI.Offset(,1)).FormulaR1C1 = _
"=RC[-1]+R[-1]C"
End If
End If
Next
End Sub
--
Charles Chickering
"A good example is twice the value of good advice."
"Charles Chickering" wrote:
> See if this works.
> Sub CopyToNumberWorksheet()
> Dim ws As Worksheet
> For Each ws In ActiveWorkbook.Worksheets
> If UCase(Left(ws.Name, 5)) = "SHEET" Then
> If IsNumeric(Right(ws.Name, Len(ws.Name) - 5)) Then
> Range("J3") = Range("I3")
> Range("J4:J6").FormulaR1C1 = "=RC[-1]+R[-1]C"
> End If
> End If
> Next
> End Sub
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "keri" wrote:
>
> > Hi,
> >
> > I am looking for code to accomplish the below but my efforts are not
> > working and i'm struggling to find a topic that is similar enough to
> > help.
> >
> > I need this code to loop through all worksheets named "sheet" & number
> > (eg sheet1,sheet2)
> >
> > I need to look in column A to find the value "1"
> > If for example "1" is found in row 3 then
> >
> > Copy I3 to J3
> > Then J4 = I4 + J3
> > Then J5 = I5 + J4
> > Then J6 = I6 + J5
> > Etc until I is blank
> >
> > Then loop to next worksheet.
> >
> >
> > Many thanks for your help in advance.
> >
> >