Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to Transfer Range Values Drawn from Tables ?

  Asked By: Michael    Date: Aug 20    Category: MS Office    Views: 905
  

I am using Excel 2000.

I am wondering if the following scenario is possible.

I construct 2 lookup tables, DestinationTable and SourceTable.

Both tables are composed of text ranges, which will be used to
transfer values. In cell F1 will be a vlookup formula drawing a range
from DestinationTable. G1 will have a vlookup table drawing a range
from SourceTable. Let's say that F1 yields '"K50:M400" and G1 yields
'"P25:R375". ( Both cells have an apostrophe ' followed by quotations
" at the start of there cell info, so the text range that appears in
them is surrounded by quotation marks.)

Using VBA, I know the following line causes the range K50:M400 on
Sheet YTD to receive the data from range P25:R375 on Sheet June.

Range("YTD!K50:M400").Value = Range("June!P25:R375").Value

In an experiment, I set-up the following code :

Dim Dest as String, Source as String
Dest = Range("Sheet1!F1").Value
Source = Range("Sheet1!G1").Value

After that, I added the line below, which did not work.

Range(Dest).Value = Range(Source).Value

I know the problem must be in finding the proper syntax to get the
Destination range to receive the Source range. What I'm after is code
which receives from a Sheet cell text Ranges to use in the manner
shown above. While I assume this is possible, I don't know how to
write this. If you could help, I would greatly appreciate it.

If this is achieved, I foresee this being very useful, as you could
utilize lookup tables to feed ranges into a single line of code. As
opposed to having to write many potential lines, each line specifying
exact ranges in them. Such as "G7:X99", "AF4:AH61", "C20:C404".

And if this not "doable", I'd like to know this as well.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Elias Turner     Answered On: Aug 20

Several points
1. Can you not lose the "" round the text in the Source and
Destination tables? The single ' at the beginning doesn't matter.
2. As it stands Dest and Source strings in vba do not contain any
Sheet name data, only row/column data. With the quotation marks
removed as suggested in (1) the macro works well copying data on
whichever sheet is the active sheet.
3. Keeping the quote marks in the tables  the following works in the
active sheet:

Dim Dest As String, Source As String
Dest = Mid(Range("Sheet1!F1").Value, 2, _
Len(Range("Sheet1!F1").Value) - 2)
Source = Mid(Range("Sheet1!G1").Value, 2, _
Len(Range("Sheet1!G1").Value) - 2)
Range(Dest).Value = Range(Source).Value

To include Sheet data you can either adjust Dest and Source by adding
that data thus:
Dest = "YTD!" & Dest
Source = "June!" & Source

before the last line in your code,

or instead change the last line to:
Range("YTD!" & Dest).Value = Range("June!" & Source).Value

In the last case, if June is the active sheet when you run the macro
you could pick up the sheet name thus:

Range("YTD!" & Dest).Value = Range(ActiveSheet.Name & "!" &
Source).Value

(that's one line)

and in the case before that as follows:

Source = ActiveSheet.Name & "!" & Source

leaving your original last line as
Range(Dest).Value = Range(Source).Value

at the end.

 
Answer #2    Answered By: Kifah Malik     Answered On: Aug 20

Reading your reply makes sense, and I will apply it later
today, after attending to an appointment.

I truly thank you for jumping on this and helping once again.

 
Didn't find what you were looking for? Find more on How to Transfer Range Values Drawn from Tables ? Or get search suggestion and latest updates.




Tagged:          

 
 
 
 

Related Post