Help with repetitive copy and paste vba

[Cherry Chui]

Hello. First let me say thank you for your help. I'm sorry if this answer is elsewhere, but I've had troubles trying to adjust other's solutions to fit my needs. Unfortunately, my skill level is that low.

What I need is to create a matrix of prices. I need to fill up every cell in the Base Price document (https://www.dropbox.com/s/wmxw3580nb5u5e1/Base Price.xls?dl=0)

By entering in certain criteria into Q_Input (https://www.dropbox.com/s/d1yr6ppak5gogje/Q_Input.xlsx?dl=0). Q_Input is part of a larger document that does all the complicated calculations but I separated it out to keep the confidential info confidential. in theory by changing 5 factors:
1. # layers
2. Lamination Type
3. Panel
4. Thickness
5. Quantity

a total is calculated and shown in cell "E6". Most likely the total won't calculate now since I separated it from the calculating part. But that is fine. I'm more concerned about copying and pasting the value from E6 into the Base price table.

I created a macros so that it will fill in the first cell by having it copy and paste the information into the table and then copy and paste the solution into the table. But, I don't know how to get it to do the same thing to the next cell and so on.

Here is my Macros:

Sub Macro17()

Macro17 Macro

Sheets("Base Price").Select
Range("E25").Select
Selection.Copy
Sheets("Q_Input").Select
Range("F16").Select
ActiveSheet.Paste


Sheets("Base Price").Select
Range("E24").Select
Selection.Copy
Sheets("Q_Input").Select
Range("F13").Select

ActiveSheet.Paste



Sheets("Base Price").Select
Range("$C$27").Select
Selection.Copy
Sheets("Q_Input").Select
Range("C14").Select

ActiveSheet.Paste



Sheets("Base Price").Select
Range("D26").Select
Selection.Copy
Sheets("Q_Input").Select
Range("F20").Select

ActiveSheet.Paste



Sheets("Base Price").Select
Range("$C$40").Select
Selection.Copy
Sheets("Q_Input").Select
Range("C13").Select

ActiveSheet.Paste



Sheets("Q_Input").Select
Range("E6").Select
Selection.Copy
Sheets("Base Price").Select
Range("E26").Select



Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Any help you can give me is much appreciated!

Sincerely,
Cherry Chui

 

[NARAYANK991]

Hi Cherry ,

I think your problem description is not very clear ; can you explain the following in detail ?

1.

What I need is to create a matrix of prices. I need to fill up every cell in the Base Price document

Does the matrix cover the range E26 : FN2275 ? This means a total of 373,500 cells !

2.

by changing 5 factors ... a total is calculated and shown in cell "E6"

How is the code going to change the 5 factors , so that the above 373,500 cells can be populated ?

Narayan

 

[Cherry Chui]

Hi Cherry ,

I think your problem description is not very clear ; can you explain the following in detail ?

1.

Does the matrix cover the range E26 : FN2275 ? This means a total of 373,500 cells !

2.

How is the code going to change the 5 factors , so that the above 373,500 cells can be populated ?

Narayan

Dear Narayan,

1. Yes. I will need it to fill 373,500 cells

2. Someone created a very very very complicated calculator tool (the 5 variabes create other variables that are calculated upon each other) that is normally attached to that Q_input sheet. So originally a person would enter in the variables into the blue cells (the is415 should be blue and the cell for Thickness should also be blue. I accidentally changed them and haven't changed them back) in the Q_input sheet and the rest of the document would calculate them and spit out a # in cell E6.

Since I have an excel doc that does the calculating accurately I figured the best way to get those 373,500 cells filled out was to create a macro that would act as the person and enter in every combination of those 5 variables into Q_input so when reattached to the calculator portion it will fill up all those cells with the correct data. Also, the code to change the 5 factors is confidential material and the truth is I wouldn't be able to explain it.

Does this help to clarify?

 

[NARAYANK991]

Hi Cherry ,

It clarifies , but given your clarification , what you want done appears to be impossible !

As I understand it , the different values of the 5 factors have to be entered into the designated cells , and then the calculator tool will generate the result in E6.

This value will then go into the corresponding cell in the matrix.

The next set of values for the 5 factors will be entered , and a new value of E6 will result.

This value will again be entered into the matrix , in the appropriate cell.

This process will be repeated till all the 373,500 cells have been populated.

Thus , while the destination changes 373,500 times , the source will always be E6.

A new value of E6 will be generated only when a particular set of values for the 5 factors has been entered into the designated cells , and the calculator tool has been run.

If you say that the code for the calculator tool is confidential , it means that who ever is developing the macro that you want , will not have access to the macro , and therefore even though the different combinations of the 5 factors can be generated , the output based on each combination will not be available to be copied and pasted into the matrix.

Is my understanding correct ?

Narayan

 

[Cherry Chui]

Dear Narayan,

Yes your understanding is correct. Yes it does seem pretty impossible! I was hoping that this way would work.

Thank You,
Cherry