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
44 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 33

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 33

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 33

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 33

Accepted Solution

by:
Norie earned 500 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 33

Expert Comment

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

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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

773 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