?
Solved

VBA Excel: Replace a range of values on a sheet with a range of values from another sheet

Posted on 2016-11-20
10
Medium Priority
?
85 Views
Last Modified: 2016-11-21
Hello Experts,

I have multiple pricing grids placed on individual sheets that are identical in terms of their ranges: A1:Q27  What I would like to do is replace my template pricing sheet Values  (Sheet name "Price_Template")  with the values from my other sheets: (Pricing_01, Pricing_02 Pricing_03 etc.
I know I can do copy > paste but I'm assuming that there is a more efficient method. Is their some kind of "Copy Sheet" or "Copy Range" command that will be useful in this type of situation?

Thank you for looking at this.
0
Comment
Question by:Member_2_7966101
  • 5
  • 4
10 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 41895209
How would it be determined which sheet to take the values for the template sheet?

Where do the prices go in the template sheet?

If it's also A1:Q27, or a similarly sized range, there could be a simple solution using INDIRECT.
0
 

Author Comment

by:Member_2_7966101
ID: 41895216
Thank you for your reply.

There are only three pricing  substitutions necessary and  each substitution occurs based on a condition. The pricing sheets  load sequentially: Pricing_01, Pricing_02, Pricing_0. Everything is running well with in terms of loading the values from the default pricing sheet. The only thing I have to do is replace my pricing sheet as each condition occurs:

1st Condition replace Price_Template sheet  values with Pricing_01 sheet values
2nd Condition replace Price_Template sheet  values with Pricing_ 02 sheet values
3rd Condition replace Price_Template sheet  values with Pricing_03 sheet values
0
 
LVL 36

Expert Comment

by:Norie
ID: 41895223
Are you currently manually copying the data from the relevant sheet based on the condition(s)?

Do you want automate the process using code?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Member_2_7966101
ID: 41895230
Yes, I have a multipage Userform that processes various listbox values  via VBA and I would like to automate this particular issue.  Thank you
0
 
LVL 36

Expert Comment

by:Norie
ID: 41895241
I'm sorry but I'm a little confused, I thought you wanted data transferred from pricing sheets to a price template.

Where does the userform come into things?

Are you using it to select which pricing data to use, or something more?
0
 

Author Comment

by:Member_2_7966101
ID: 41895245
Thank you for the follow up. I just mentioned the userform in passing. My VBA code pertains to one of the pages of the userform, that's all. The only thing I am trying to do is use code to transfer the values FROM range A1:Q27  on sheet  "Pricing_01" TO sheet  "Pricing_Template." based on a condition. Later on, another condition occurs at which point I need to transfer values FROM range A1:Q27  on sheet  "Pricing_02" TO sheet  "Pricing_Template and then finally a 3rd condition where I need to transfer values FROM range A1:Q27  on sheet  "Pricing_03" TO sheet  "Pricing_Template.
0
 
LVL 36

Accepted Solution

by:
Norie earned 2000 total points
ID: 41895257
Transferring the data shouldn't be a problem, can you give more details?

When do the values need to be changed?

Where on the template sheet do the values go?

What is the 'condition'?

How/when does it change?
0
 

Author Comment

by:Member_2_7966101
ID: 41895262
I figured it out. I couldn't see the forest for the trees I guess. This seems to work:


If condition1 is true:

ActiveWorkbook.Sheets("Price_Template ").Range("A1:Q27").Value = ActiveWorkbook.Sheets("Pricing_01 ").Range("A1:Q27").Value

If condition2 is true:

ActiveWorkbook.Sheets("Price_Template ").Range("A1:Q27").Value = ActiveWorkbook.Sheets("Pricing_02 ").Range("A1:Q27").Value


If condition3 is true:

ActiveWorkbook.Sheets("Price_Template ").Range("A1:Q27").Value = ActiveWorkbook.Sheets("Pricing_03 ").Range("A1:Q27").Value


Thank you for your time on this
0
 
LVL 36

Expert Comment

by:Norie
ID: 41895268
No problem.:)
0
 
LVL 34

Expert Comment

by:Rob Henson
ID: 41895555
You could have done it with a formula instead; in A1:

=CHOOSE(Condition,Pricing_01!A1,Pricing_02,A1,Pricing_03,A1)

Condition refers to a cell with a formula/hard-coded 1 to 3 to determine which condition is currently in place. If Condition is a standard cell reference, you will have to lock it with $ or you could make it a named cell.

Copy from A1 and paste across and down to Q27.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question