Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2016-11-20
Medium Priority
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.
Question by:Member_2_7966101
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 35

Expert Comment

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.

Author Comment

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
LVL 35

Expert Comment

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?
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

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
LVL 35

Expert Comment

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?

Author Comment

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.
LVL 35

Accepted Solution

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?

Author Comment

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
LVL 35

Expert Comment

ID: 41895268
No problem.:)
LVL 33

Expert Comment

by:Rob Henson
ID: 41895555
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.

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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