The bottom part of the code I pasted in the last post had
the copy/paste code. Shouldn't this work

'Copy original range to new range off by 1 column
sh.UsedRange.Copy Destination:= _
Worksheets(sh2).Range(TempSh2PasteCell)

'Copy original range to new range off by 1 row
sh.UsedRange.Copy Destination:= _
Worksheets(sh3).Range(TempSh3PasteCell)


-----Original Message-----
Isn't that because you code doesn't do any pasting? You
are just getting the
value in the other sheets offset with the code as it
stands.

--

HTH

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


"ExcelMonkey"
wrote in message
...
Sorry I had some variable name inconsitencies. But
cannot seem to paste to tem sheets. Nothing shows up
after copy paste



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
Set TempSh2PasteCell = Worksheets
(TempSh2Name).Cells(CopyStartRow, CopyStartCol + 1)
'Set Paste cell off 1 row below
Set TempSh3PasteCell =
Worksheets 'Copy original range to new range
off by 1 column
sh.UsedRange.Copy Destination:= _
Worksheets(sh2).Range(TempSh2PasteCell)

'Copy original range to new range off by 1 row
sh.UsedRange.Copy Destination:= _
Worksheets(sh3).Range(TempSh3PasteCell)
(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol)



Next


-----Original Message-----
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


.



.