Passing Cell Address (String or Range)

[ExcelMonkey]

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

 

[Bob Phillips]

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]

So I am now trying to copy and paste the original sheets
UsedRange to the defined past cells. But its not
working. Nothing is pasted. Why is that?

For Each sh In ActiveWorkbook.Worksheets
'Set up past ranges in temp sheets
CopyStartCellRow = sh.UsedRange.Cells(1, 1).Row
CopyStartCellCol = 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)

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

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

 

[ExcelMonkey]

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
(TempSh3Name).Cells(CopyStartRow + 1, CopyStartCol)

'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)

Next

 

[Bob Phillips]

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]

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)

Worksheets>> 'Copy original range to new range
off by 1 column

 

[Bob Phillips]

Sorry, I was still looking at the original code.

I must admit to not fully following the code, but these lines

TempSh2PasteCell = Worksheets(TempSh2Name).Cells(CopyStartRow, CopyStartCol
+ 1)
TempSh3PasteCell = Worksheets(TempSh3Name).Cells(CopyStartRow + 1,
CopyStartCol)

aren't geting a cell as I think you believe, they are getting a cell value.
You need to declare them as ranges and set them. Also, the copy can then
just use that range, as it includes the sheet.

Try this

Dim sh, CopyStartRow, CopyStartCol
Const TempSh2Name = "Sheet2"
Const TempSh3Name = "Sheet3"
Dim TempSh2PasteCell, TempSh3PasteCell

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(TempSh3Name).Cells(CopyStartRow +
1, CopyStartCol)

'Copy original range to new range off by 1 column
sh.UsedRange.Copy Destination:=TempSh2PasteCell

'Copy original range to new range off by 1 row
sh.UsedRange.Copy Destination:=TempSh3PasteCell

Next


--

HTH

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