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.
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?
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.
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
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?
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
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?
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.
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
NorieVBA ExpertCommented:
No problem.:)
Rob HensonFinance AnalystCommented:
You could have done it with a formula instead; in 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.
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.