Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Macro for Fill In

0

Dear All,

I have an excel sheet that contains 3 columns of Data: M:O.

What i need is a macro that starts from M5 moves to P5 and prints a "1" in P5 and

then copies from M5 and special pastes values in D6,

then Copies from N5 and special Pastes values in G2,

then copies from  O5 and special pastes values in H7

macro ends with selecting data from A1:I14

i will then print the matter and now select M6 and run the macro.

The macro will now again starts from M6 moves to P6 and prints a "1" in P6 and 

then copies from M6 and special pastes values in D6,

then Copies from N6 and special Pastes values in G2,

then copies from  O6 and special pastes values in H7

macro ends with selecting data from A1:I14

Everytime the source of copy will move one cell down (untill the data list is exhausted) but the paste destination will remain the same.

I know its very basic for many of you all, but I am new at this.

Any Help will be appreciated.

excel
MS Office Version: 365
posted by: asif_187 (rep: 6) on Fri Dec 23, 2022 at 3:52 am
Answer
Discuss

Discussion

Hi Asif

It doesn't normally work that way (you specify a mini-project and we write the code for you) but please attach a sample Excel file to your question (Edit it and use the Add Files... button). Note that it may be a while before I can answer (owing to Christmas activities) but others might.
John_Ru (rep: 6142) Dec 23, '22 at 10:37 am
Add to Discussion

Answers

0

Hello asif_187,

Just as John has stated, we would like posters to first themselves try to get the answer they want before seeking our advice. But I had a bit of free time today so I thought I would give you a late Christmas present.

To do what you want is actually quite simple and can be achieved with just one macro.

(revised file also attached)


Sub Copy_Print_Data()

' ++++++++++++++++++++++++
' macro written by WillieD24 for TeachExcel
' this macro will copy each row of data to specific cells then print that data
' then repeat until all rows have been copied and printed
' ++++++++++++++++++++++++

Dim LRow As Long   ' last row in column "M" with data
Dim CopyRow As Long   ' row number of data to be copied


LRow = Range("M" & Rows.Count).End(xlUp).Row
CopyRow = 5    ' first row with data to copy

Do Until CopyRow > LRow

    Range("D6").Value = Cells(CopyRow, 13).Value ' header
    Range("G2").Value = Cells(CopyRow, 14).Value  ' date
    Range("H7").Value = Cells(CopyRow, 15).Value  ' amt

    Range("A1:L14").PrintOut     ' print to default printer

    Cells(CopyRow, 16).Value = 1  ' enter a " 1 " in column "P" of current row

CopyRow = CopyRow + 1

Loop   ' repeat until all data has been copied and printed

End Sub

Also,

Column "G" will need to be reformatted to the date style you prefer and proper width.

Printing "A1:L14" requires 2 sheets of 8-1/2 x 11 paper; change layout to Landscape or change paper size so entire range will print on one sheet of paper.

Row3 is hidden but it contains no data or formulas?

Happy New Year

|Cheers   :-)

posted by: WillieD24 (rep: 557) on Fri Dec 30, 2022 at 3:04 pm
Discuss

Discussion

Hi Asif. Where's your comment on this Answer (or its selection)? You said "Any help will be appreciated" but it feels like you just wasted Willie's time.
John_Ru (rep: 6142) Jan 7, '23 at 2:44 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login