Dealing with Worksheet Names

[Jim Berglund]

I'm trying to copy from various parts of a succession of worksheets. Everything is in the same file.


My worksheets are named 1,2,3,4,5
I want to copy the contents from C4 in worksheets 2,3,4,5 into column 3 in WS1; then I want to copy the contents from C6 to column 4.

It's been a horror show - I'm dealing with formulas like ='[C3]1'$C$4 and each cell has to vertify the directory & file.

Please help...

Jim Berglund

[JE McGimpsey]

One way:

Public Sub CopyC4sAndC6sToSheet1()
Dim rDest As Range
Dim i As Long
Set rDest = Sheets("1").Range("C1")
For i = 2 To 5
With Sheets(CStr(i))
rDest.Offset(i - 2, 0).Value = .Range("C4").Value
rDest.Offset(i - 2, 1).Value = .Range("C6").Value
End With
Next i
End Sub




In article <C2oRe.346282$s54.294776@pd7tw2no>,
"Jim Berglund" <berglunj@richer.ca> wrote:

> I'm trying to copy from various parts of a succession of worksheets.
> Everything is in the same file.
>
>
> My worksheets are named 1,2,3,4,5
> I want to copy the contents from C4 in worksheets 2,3,4,5 into column 3 in
> WS1; then I want to copy the contents from C6 to column 4.
>
> It's been a horror show - I'm dealing with formulas like ='[C3]1'$C$4 and
> each cell has to vertify the directory & file.
>
> Please help...
>
> Jim Berglund