Copy contents of Range to other cell

[Al]

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

 

[ijb]

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

 

[Don Guillett]

I'm confused

 

[Don Guillett]

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)

 

[GB]

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

 

[ijb]

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

 

[GB]

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

 

[Don Guillett]

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

 

[GB]

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

 

[Dianne]

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(strAddress, "'", "")
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

 

[Don Guillett]

So solly, Didn't read that part.

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