Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al

something along these lines should work (or at least get you going in the
right direction )

Sub copyrange()
Dim rngMyRange As Range
Set rngMyRange = Worksheets("Customer Information").Range("AG3:AS3")
rngMyRange.Copy
Sheets("Bill").Activate
Cells(28, 1).Select
ActiveSheet.Paste
End Sub


"Al" wrote in message
...
Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al

If that's what is meant then maybe this ONE liner would work.
Worksheets("Customer Information").Range("AG3:AS3").copy
Sheets("Bill").Cells(28, 1)


"ijb" wrote in message
...
something along these lines should work (or at least get you going in the
right direction )

Sub copyrange()
Dim rngMyRange As Range
Set rngMyRange = Worksheets("Customer Information").Range("AG3:AS3")
rngMyRange.Copy
Sheets("Bill").Activate
Cells(28, 1).Select
ActiveSheet.Paste
End Sub


"Al" wrote in message
...
Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al

Sorry, I thought he wanted to see how a range object could be used to solve
the problem, perhaps I mis-understood



"Don Guillett" wrote in message
...
If that's what is meant then maybe this ONE liner would work.
Worksheets("Customer Information").Range("AG3:AS3").copy
Sheets("Bill").Cells(28, 1)


"ijb" wrote in message
...
something along these lines should work (or at least get you going in
the
right direction )

Sub copyrange()
Dim rngMyRange As Range
Set rngMyRange = Worksheets("Customer Information").Range("AG3:AS3")
rngMyRange.Copy
Sheets("Bill").Activate
Cells(28, 1).Select
ActiveSheet.Paste
End Sub


"Al" wrote in message
...
Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al

I'm confused

"Al" wrote in message
...
Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al

"Don Guillett" wrote in message
...
I'm confused

Hi I think that what Al wants is something along the lines of:

Range(Range("Bill!A1")).Copy Destination:=Range("Bill!A28")

This assumes that Bill!A1 is his originating cell which contains :
'Customer Information'!AG3:AS3

Unfortunately that does not quite work, and I am not sure why it does not
work. It is something to do with the contents of A1 not being read properly
as a string and converted into a cell address, but I cannot see what is
wrong. Perhaps some wise angel will guide me.

Geoff

"GB" wrote in message
...

"Don Guillett" wrote in message
...
I'm confused

Hi I think that what Al wants is something along the lines of:

Range(Range("Bill!A1")).Copy Destination:=Range("Bill!A28")

This assumes that Bill!A1 is his originating cell which contains :
'Customer Information'!AG3:AS3

Unfortunately that does not quite work, and I am not sure why it does not
work. It is something to do with the contents of A1 not being read
properly
as a string and converted into a cell address, but I cannot see what is
wrong. Perhaps some wise angel will guide me.

Geoff


Got it - anyway, this works when I test it.

Range("'" & Range("Bill!A1")).Copy Destination:=Range("Bill!A28")

Incidentally "'" is double-quote then single-quote then double-quote, just
in case that's not obvious

Geoff

try
[bill!a1].copy [bill!a28]
or for just the value
[bill!a28]=[bill!a1]

"GB" wrote in message
...

"Don Guillett" wrote in message
...
I'm confused

Hi I think that what Al wants is something along the lines of:

Range(Range("Bill!A1")).Copy Destination:=Range("Bill!A28")

This assumes that Bill!A1 is his originating cell which contains :
'Customer Information'!AG3:AS3

Unfortunately that does not quite work, and I am not sure why it does not
work. It is something to do with the contents of A1 not being read
properly
as a string and converted into a cell address, but I cannot see what is
wrong. Perhaps some wise angel will guide me.

Geoff

"Don Guillett" wrote in message
...
try
[bill!a1].copy [bill!a28]
or for just the value
[bill!a28]=[bill!a1]

Beg your pardon Don, but doesn't that just copy cell A1 to A28. What the OP
wanted was to look in Cell A1, read it's contents and use that as the range
to copy from, then copy *that* range to A28. At least that was what I
thought he wanted.

GB

So solly, Didn't read that part.

"GB" wrote in message
...

"Don Guillett" wrote in message
...
try
[bill!a1].copy [bill!a28]
or for just the value
[bill!a28]=[bill!a1]

Beg your pardon Don, but doesn't that just copy cell A1 to A28. What the
OP
wanted was to look in Cell A1, read it's contents and use that as the
range
to copy from, then copy *that* range to A28. At least that was what I
thought he wanted.

GB

OK, this is a bit long-winded, and I'm fairly sure someone will come up
with a nice one-liner, however...

Assumes that the cell that contains the range is on Sheet1, cell A1;
change as appropriate.

Sub CopyMyRange()

Dim strAddress As String
Dim strSheetName As String
Dim intPosExcl As Integer

With ActiveWorkbook
strAddress = .Worksheets("Sheet1").Range("A1").Value
strAddress =
Application.WorksheetFunction.Substitute(strAddres s, "'", "")
intPosExcl = InStr(1, strAddress, "!")
If intPosExcl = 0 Then Exit Sub
strSheetName = Left(strAddress, intPosExcl - 1)
strAddress = Mid(strAddress, intPosExcl + 1)
.Worksheets(strSheetName).Range(strAddress).Copy _
Destination:=.Worksheets("Bill").Range("A28")
End With

End Sub

--
Dianne

In ,
Al typed:
Please help! I have a spreadsheet that lists, in a cell,
the Worksheet/Range of pertinent information that I want
to use. I want to be able to store this Worksheet/Range
in a variable and then use it to copy the information that
is located within that Worksheet/Range into a cell.

ie. the originating cell has:
'Customer Information'!AG3:AS3 (store this in a
variable) and copy the information that is located at that
location to the target worksheet/cell: 'Bill'!A28

In other words, I don't want 'Customer Information'!
AG3:AS3 in the target cell, I want the information
contained in that range copied to the target cell.

I want to use VBA to code this if possible? Thank you in
advance for any help you can provide.

Al