Need help with creating a special copy and paste macro
Hello and Help!
I have a 3 workbooks with 6 worksheets in each. Each worksheet contains about 20-25000 rows of information! These workbooks need to be updated monthly. If you do the math, that's a lot to sort through and update. It use to take almost a month to update everything for the last month. I have been able to take it down to a little under 2 weeks. Here's a sample: STYLE DESCRIPTION FEB MAR APR MAY JUN JUL TOTAL XXXXXXXX ABC123 SALES ACTUAL 0 0 0 0 0 SALES PROJ 0 0 0 0 0 SALES PROJ 0 0 0 0 0 SALES LY 0 0 0 0 0 Chg Act/LY 0 0 0 0 0 BOM ACTUAL 0 0 0 0 0 BOM LY 0 0 0 0 0 Chg Act/LY 0 0 0 0 0 PROJ REC DIV 0 0 0 0 0 PROJ REC FMG 0 0 0 0 0 DIFFERENCE 0 0 0 0 0 ORDERED 0 0 0 0 0 SHIPPED 0 0 0 0 0 % FILL 0 0 0 0 0 ORD/PROJ 0 0 0 0 0 VEND PROJ REC 0 0 0 0 0 VEND BOM ONH 0 0 0 0 0 VEND ACT REC 0 0 0 0 0 PROJ REC/ACT REC 0 0 0 0 0 Some of the information is calculated, the rest is entry off vlookups of different pivot tables. What I would like is a VBA that would allow me to copy the vlookup by row in the correct column I need and then change those vlookups in to value. For example: if I need the VEND ACT REC for July, I would like to have a box that would allow me to specify that for all rows that have VEND ACT REC to copy and paste a particular forumula in the column of my choosing. I guess I would need a box that would ask me what I am looking for by row and column. I guess the next line in the box would ask me what formula I would like to copy and paste into the rows. The next box would ask me which column do I need to start the copy and paste in (because the rows would be any that have VEND ACT REC or any variable that I desire). I would like to have a box that I can check off if I want to change the forumlas to values after done. I also need the formula to correctly reference the correct cell in the vlookup. If the vlookup in the example will be using $A3 as the value to lookup, when the formulas are copied and pasted, the next cell to reference in the example would be $A24 (consider a line skipped). I hope someone can help me. Thanks Shawn |
Need help with creating a special copy and paste macro
You can prompt for cell locations using
Dim rng as Range On error resume next set rng = Application.Inputbox( Type:=8) On Error goto 0 if not rng is nothing then msgbox "you selected " & rng.address End if as an example. copying formulas follows the same adjustment rules as doing it manually (relative and absolute references). If those don't work for you, then you would have to develop the rules and implement them on how to adjust the formulas. -- Regards, Tom Ogilvy "Shawn" wrote in message om... Hello and Help! I have a 3 workbooks with 6 worksheets in each. Each worksheet contains about 20-25000 rows of information! These workbooks need to be updated monthly. If you do the math, that's a lot to sort through and update. It use to take almost a month to update everything for the last month. I have been able to take it down to a little under 2 weeks. Here's a sample: STYLE DESCRIPTION FEB MAR APR MAY JUN JUL TOTAL XXXXXXXX ABC123 SALES ACTUAL 0 0 0 0 0 SALES PROJ 0 0 0 0 0 SALES PROJ 0 0 0 0 0 SALES LY 0 0 0 0 0 Chg Act/LY 0 0 0 0 0 BOM ACTUAL 0 0 0 0 0 BOM LY 0 0 0 0 0 Chg Act/LY 0 0 0 0 0 PROJ REC DIV 0 0 0 0 0 PROJ REC FMG 0 0 0 0 0 DIFFERENCE 0 0 0 0 0 ORDERED 0 0 0 0 0 SHIPPED 0 0 0 0 0 % FILL 0 0 0 0 0 ORD/PROJ 0 0 0 0 0 VEND PROJ REC 0 0 0 0 0 VEND BOM ONH 0 0 0 0 0 VEND ACT REC 0 0 0 0 0 PROJ REC/ACT REC 0 0 0 0 0 Some of the information is calculated, the rest is entry off vlookups of different pivot tables. What I would like is a VBA that would allow me to copy the vlookup by row in the correct column I need and then change those vlookups in to value. For example: if I need the VEND ACT REC for July, I would like to have a box that would allow me to specify that for all rows that have VEND ACT REC to copy and paste a particular forumula in the column of my choosing. I guess I would need a box that would ask me what I am looking for by row and column. I guess the next line in the box would ask me what formula I would like to copy and paste into the rows. The next box would ask me which column do I need to start the copy and paste in (because the rows would be any that have VEND ACT REC or any variable that I desire). I would like to have a box that I can check off if I want to change the forumlas to values after done. I also need the formula to correctly reference the correct cell in the vlookup. If the vlookup in the example will be using $A3 as the value to lookup, when the formulas are copied and pasted, the next cell to reference in the example would be $A24 (consider a line skipped). I hope someone can help me. Thanks Shawn |
Need help with creating a special copy and paste macro
Thanks Tom, I'm still in the process of learning how to right application. I will try to figure this all out. Thanks again for the start. It can only get better from this point. I have just one question: In the Application.Inputbox, you typed"( Type:=8)", is it written exactly as that or just "type=8". And is setting the type of input box I have? Thanks Shawn *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Need help with creating a special copy and paste macro
Arguments are set using colon equal
set rng = Application.InputBox(Prompt:="Select a range with the mouse", _ Type:=8) put this in a module, then highlight Application.InputBox and hit F1 to get help on the function. This should show you what other agruments you have available. It also will explain what the type argument does. Note that Application.Inputbox gives you the Excel inputbox which is different from the VBA inputbox. ans = InputBox(Prompt:="Type your name") is different from ans = Application.InputBox(Prompt:="Type your name", Type:=2) although the differences may be subtle for many uses. The application.Inputbox actually acts as a virtual cell, so you could type in a formula same as in a cell and it would be evaluated. so for the example, where a string is expected, I could type in ="ABC " & G9 ans would hold the concatenation of the string "ABC " and whatever is in cell G9 of the active sheet. -- Regards, Tom Ogilvy "Shawn Hobbs" wrote in message ... Thanks Tom, I'm still in the process of learning how to right application. I will try to figure this all out. Thanks again for the start. It can only get better from this point. I have just one question: In the Application.Inputbox, you typed"( Type:=8)", is it written exactly as that or just "type=8". And is setting the type of input box I have? Thanks Shawng *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
Need help with creating a special copy and paste macro
Thanks a million. *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded for it! |
All times are GMT +1. The time now is 11:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com