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

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.
Member_2_7966101Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
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
 
NorieVBA ExpertCommented:
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
 
Member_2_7966101Author Commented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
NorieVBA ExpertCommented:
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
 
Member_2_7966101Author Commented:
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
 
NorieVBA ExpertCommented:
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
 
Member_2_7966101Author Commented:
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
 
Member_2_7966101Author Commented:
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
 
NorieVBA ExpertCommented:
No problem.:)
0
 
Rob HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.