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

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" 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