One way

For Each sh In ActiveWorkbook.Worksheets
'Set up past ranges in temp sheets
CopyStartRow = sh.UsedRange.Cells(1, 1).Row
CopyStartCol = sh.UsedRange.Cells(1, 1).Column
'Set Paste cell off 1 column to the right
TempSh2PasteCell = Worksheets(TempSh2Name).Cells(CopyStartRow,
CopyStartCol + 1)
'Set Paste cell off 1 row below
TempSh3PasteCell = Worksheets(TempSh3Name).Cells(CopyStartRow + 1,
CopyStartCol)
Next

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ExcelMonkey" wrote in message
...
I have 3 sheets. The firs is my main sheet, the 2nd and
third are temp sheets whihc I have created. I am copying
data from sheet 1 to the temp sheets. I want to copy to
temp1 by offseting by 1 volumn then to temp 2 by
offseting by 1 row. Currently having trouble setting up
the copy an paste range to do this.

I am tryint pass the cell address from sheet to a
varible. I then want to used this address with an offset
to set two other variables. I can pass the address to
the first variable by making the first variable a
string. However, this does not all be to pass offset
version of this address to the 2nd and 3rd variables. I
am assuming that this is because they are dimensioned as
ranges. Should I be defineing them all as ranges and use
the Set stmt?


Dim CopyStartCell As String
Dim TempSh2PasteCell As Range
Dim TempSh3PasteCell As Range

For Each sh In ActiveWorkbook.Worksheets
'Set up past ranges in temp sheets
CopyStartCell = sh.UsedRange.Cells(1, 1).Address
'Set Paste cell off 1 column to the right
TempSh2PasteCell = Worksheets(TempSh2Name).Range
(CopyStartCell).Offset(0, 1)
'Set Paste cell off 1 row below
TempSh3PasteCell = Worksheets(TempSh3Name).Range
(CopyStartCell).Offset(1, 0)
Next