Member_2_7971385
asked on
Copy certain cells from one file paste to another then print using a macro.
I'm new to Excel Macros and VBA so thank you in advance for any help.
I have an excel report that has multiple rows and each day the number of rows changes. What I want to do is copy certain cell data from each row in this report and paste that data into certain cells of a different work book, then have that work book print.
In hopes of simplifying it, It would go something like this: Workbook1: copy cells C9, F9, H9 Workbook2: Paste into cells C10,C11,C12 respectively then print Workbook2. This would then move to the next row in Workbook1 and continue until it finds empty rows.
Items that change daily are Workbook1 name and the number of rows in Workbook1. The cells that I'm copying from and pasting to and the name of Woorkbook2 would all be static.
I know the copy and paste function is possible but I'm not sure about the printing or making it move row by row until it finds empty rows.
Again, Thanks in advance for any help.
I have an excel report that has multiple rows and each day the number of rows changes. What I want to do is copy certain cell data from each row in this report and paste that data into certain cells of a different work book, then have that work book print.
In hopes of simplifying it, It would go something like this: Workbook1: copy cells C9, F9, H9 Workbook2: Paste into cells C10,C11,C12 respectively then print Workbook2. This would then move to the next row in Workbook1 and continue until it finds empty rows.
Items that change daily are Workbook1 name and the number of rows in Workbook1. The cells that I'm copying from and pasting to and the name of Woorkbook2 would all be static.
I know the copy and paste function is possible but I'm not sure about the printing or making it move row by row until it finds empty rows.
Again, Thanks in advance for any help.
Roy Cox
Why move to individual rows? It would be quicker to copy all the cells in action if they are all in the same column. Provide a dummy workbook with before and after examples.
also consider hiding columns of the source workbook, then printing that.
use the macro recorder to try it out and we can help improve the macro,
use the macro recorder to try it out and we can help improve the macro,
ASKER
I apologize. I may have over simplified my example. In the original report (workbook1) there are normally around 15 rows. Sometimes more sometimes less. Each row is a high priority item for for a specific dealer. I only need certain cells out of each row and not the whole row. What I'm trying to avoid is the end user having to hand write the data that they need (Date, Dealer,Style,Serial Number) on a "hold for special inspection" form that is placed on the item for further inspection. This is all in hopes of saving time and eliminating human error.
I have attached a sample of the report that the cells need to be copied from and a sample of the form that they need to be copied to. I've Color coded what needs to go where. Again, each row is a separate item with a separate form to be printed out.
hpreport34.xlsx
SpecialInsp.xlsm
I have attached a sample of the report that the cells need to be copied from and a sample of the form that they need to be copied to. I've Color coded what needs to go where. Again, each row is a separate item with a separate form to be printed out.
hpreport34.xlsx
SpecialInsp.xlsm
ASKER
The copy and paste isn't a problem. I just can't figure out how to go row by row and print after each.
Again, thanks for any help.
Again, thanks for any help.
What about ABC Corp which has data on two Rows?
ASKER
Each row is a different serial number / product and needs to have the form printed out to be held for special inspection.
I have written some code for you. I'll post it later when I have had time to test it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That looks great. The only thing I see is it pulled the wrong cell over for the "Dealer" Line. Also, will it continue to the next row after the first row has printed and so on? I would rather have the code and button in the form as the report is emailed from another system and changes daily.
This probably wasn't the best first VBA project to tackle but I really appreciate the help.
This probably wasn't the best first VBA project to tackle but I really appreciate the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That is exactly what I needed it to do. Thank you again!
No problem, pleased to help