Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Copy certain cells from one file paste to another then print using a macro.

Posted on 2016-08-12
12
Medium Priority
?
45 Views
Last Modified: 2016-08-18
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.
0
Comment
Question by:Member_2_7971385
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41754598
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.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41754641
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,
0
 

Author Comment

by:Member_2_7971385
ID: 41756326
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Member_2_7971385
ID: 41759669
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.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41759791
What about ABC Corp which has data on two Rows?
0
 

Author Comment

by:Member_2_7971385
ID: 41759806
Each row is a different serial number / product and needs to have the form printed out to be held for special inspection.
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41759879
I have written some code for you. I'll post it later when I have had time to test it
0
 
LVL 22

Assisted Solution

by:Roy Cox
Roy Cox earned 2000 total points
ID: 41759892
I think this works.

I have put the code in the data workbook and edited the form workbook by removing the mergedcells - never use them they usually create problems.

Run the code and it will prompt you to choose the form workbook and will then print preview each report.

I can move the code to the form if you want and once you are satisfied we can change print preview to print.

I'll check back later
hpreport34.xlsm
Form.xlsm
0
 

Author Comment

by:Member_2_7971385
ID: 41761274
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.
0
 
LVL 22

Accepted Solution

by:
Roy Cox earned 2000 total points
ID: 41761320
I have moved and re-written the code for you. The button is now in the forms workbook and I have set it not to print.

I have made some corrections to the code so I think it will now run as you want.
Form.xlsm
hpreport34.xlsx
0
 

Author Comment

by:Member_2_7971385
ID: 41761856
That is exactly what I needed it to do. Thank you again!
0
 
LVL 22

Expert Comment

by:Roy Cox
ID: 41761989
No problem, pleased to help
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

719 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