Macro for copy paste to run with a formula

[ramaswamyanand]

Hi All,
Am stuck on with a problem.
I have a workbook with 2 sheets. Sheet 1-named Final sheet and sheet 2-named Base sheet.

the base sheet has values for package 1, package 2....Package 12.

I have put in IF and AND conditions in Final sheet to get package 1, package 2.... 12 in a drop down list format in Cell E5.


My requirement is-
When the values in cell E5 changes, it should activate a macro which will copy the respective package from base sheet to final sheet.

is there a way it can be done?

 

[Peter_SSs]

ramaswamyanand

Welcome to the MrExcel board!

Can you tell us more about the layout of the base sheet. Which cells are the package 1, package 2 etc in and where is the other data you want to retrieve?

Do you really need a macro? Depending on your answer to the above question, a standard lookup function can possibly do what you want.

Also, where on the Final sheet should the retrieved info go?

 

[Andrew Poulsom]

Some questions:

1. Please describe the layout of your data on sheet base, giving range references.
2. What is the reference of the top left cell on sheet Final where you want to copy the data?

 

[ramaswamyanand]

Trying to describe the sheets:

1) Final sheet- contains the final results
B5,C5,D5 have lists (drop down)
E5 has a formula result (where is say if B5=X,C5=Yand D5=X, then it shows "package 1")

2) Base sheet- contains the raw data which needs to come to final sheet as and when the E5 cell changes from package 1 to package 2 etc

the range which needs to be copied is
for package 1- A82:M117
for package 2- A119-M159 (and so on)

The question is-
When the result in Final sheet E5 shows Package 1, i should get the data in package 1 (which is A82: M117) in final sheet A20 cell.

Hope this is a bit more clear! (am very very poor in explaining it )

Thanks!

ramaswamyanand

Welcome to the MrExcel board!

Can you tell us more about the layout of the base sheet. Which cells are the package 1, package 2 etc in and where is the other data you want to retrieve?

Do you really need a macro? Depending on your answer to the above question, a standard lookup function can possibly do what you want.

Also, where on the Final sheet should the retrieved info go?

 

[Peter_SSs]

the range which needs to be copied is
for package 1- A82:M117
for package 2- A119-M159 (and so on)

So the package ranges are different sizes?

package 1: rows 82:117 = 36 rows
package 2: rows 119:159 = 41 rows

How would we know where each range starts and finishes?

 

[ramaswamyanand]

opps...
the ranges are of different sizes-
package 1 starts from
A82:M116
Package 2 : A119: M 159

so here is my thought about the probable solution to the problem. (pardon me for i cant put it in exact words!)

if there is a macro- say macro Package 1- which copies and pastes the package 1 range to "final sheet" which can be called when the E5 cell in Final sheet is "package 1"
and a macro- say Macro Package 2- which copies and pastes package 2 range to final sheet which can be called when the E5 cell in final sheet is Package 2
and so on, i guess it would do the trick...

So the package ranges are different sizes?

package 1: rows 82:117 = 36 rows
package 2: rows 119:159 = 41 rows

How would we know where each range starts and finishes?

 

[Peter_SSs]

You didn't answer the final question of my first post or the final question of Andrew's post. Both questions were aimed at finding out exacyly where on the "Final sheet" you wanted the info pasted. I have assumed then that you wanted it pasted starting at cell A6. If that is not what you want, try adjusting the value of "fr" in the code and/or the column letter in the "Destination" line of code.

Also, you said there were 12 packages but you only gave us the row references for packages 1 and 2 and no way to determine the others dynamically, so you would need to fill out the "PackageRows" array in the code to accomodate the other 10 packages.

To implement the code below, right click the "Final sheet" name tab and choose "View Code". Then copy and paste the code below into the main right hand pane that open in the VB window. Close the VB window and make selections in your drop down.

If it doesn't work, please try to provide details about what way it failed and also post the formula you have in cell E5.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">Dim</SPAN> fr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> PackageNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bs <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> bs = Sheets("Base sheet")<br>    PackageRows = Array(, "82:116", "119:159") <SPAN style="color:#007F00">'<--Fill out for all 12 Packages</SPAN><br>    fr = 6<br>    lr = UsedRange.Row + UsedRange.Rows.Count - 1<br>    <SPAN style="color:#00007F">If</SPAN> lr < 6 <SPAN style="color:#00007F">Then</SPAN> lr = 6<br>    Rows(fr & ":" & lr).ClearContents<br>    <SPAN style="color:#00007F">With</SPAN> Range("E5")<br>        <SPAN style="color:#00007F">If</SPAN> Left(.Value, 8) = "Package " <SPAN style="color:#00007F">Then</SPAN><br>            PackageNo = Val(Mid(.Value, 9, 2))<br>            Intersect(bs.Columns("A:M"), bs.Rows(PackageRows(PackageNo))).Copy _<br>                Destination:=Cells(fr, "A")<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> bs = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>