Passing Cell Address (String or Range)

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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








>-----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" <[email protected]>
wrote in message
>news:[email protected]...
>> 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
>
>
>.
>

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


>-----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" <[email protected]>
wrote in message
>news:[email protected]...
>> 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
>
>
>.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
> >-----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" <[email protected]>
> wrote in message
> >news:[email protected]...
> >> 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
> >
> >
> >.
> >

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" <[email protected]>
wrote in message
>news:[email protected]...
>> 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" <[email protected]>
>> wrote in message
>> >news:[email protected]...
>> >> 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
>> >
>> >
>> >.
>> >
>
>
>.
>

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)


"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]>
> wrote in message
> >news:[email protected]...
> >> 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" <[email protected]>
> >> wrote in message
> >> >news:[email protected]...
> >> >> 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
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >