How to Copy Data from one Excel Worksheet to another and Print

Microsoft Excel VBA Help, How do I print a different sheet?
I have 3 standard sheets in my workbook. I have data in sheet1. From this data I want to create a report based on certain criteria in sheet2 and then print the report in sheet2. I want to use two command buttons in sheet 1. The first command button will create the report in sheet2 and the second button will print the report. What string of code do I need to create a report and then print the sheet2 report? At present I’m working in Excel 2003.
Harry Gonsalves (Goa, India)
The macro code for both the copy (command button 1) and print (command button 2) command buttons is given below:

Private Sub CommandButton1_Click()
x = 2
Do While Cells(x, 1) <> “”
If Cells(x, 4) >= 20000 Then
Worksheets(“Sheet1”).Rows(x).Copy
Worksheets(“Sheet2”).Activate
eRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets(“Sheet2”).Rows(eRow)
End If
Worksheets(“Sheet1”).Activate
x = x + 1
Loop
End Sub

Private Sub CommandButton2_Click()
Sheets(“Sheet2”).PrintOut Copies:=1
End Sub

Watch the training video below to learn how to copy data from one worksheet to another in Excel and print the report using macros attached to command buttons:


Further reading:
Excel – Copy data from one sheet to another
How to Use Excel VBA for Printing

7 thoughts on “How to Copy Data from one Excel Worksheet to another and Print

  1. Marky

    Hello Sir,

    I am a newbie in VBA for excel. I truly need your help.
    What code do I need to write if I want to create different csv file that will only contain the header and it value. Base on your video above, For example:

    File1.csv
    Name Salary Perks MonthlyPackages
    John 12000 7200 19200

    File2.csv
    Name Salary Perks MonthlyPackages
    Mary 15000 9000 24000

    File3.csv
    Name Salary Perks MonthlyPackages
    Linda 10000 6000 16000

    and soon on. until in the end of the list.
    Thank you.

    Reply
  2. Ebenezer Adu Amoah

    Dear Sir
    Thank you very much for helping me to get a code to protect my school fees entry sheet at which at the end of the day, the account officer cannot decrease the amount paid by students. God bless you very much because the problems in the school regarding fees payment have been reduced by 60%.

    Another problem is that when receipt is issued to the students/children to be given to their parent, they lost it on their way to the house which also poses a lot of problems for the school. I therefore want you to help me in a situation where by after payment of fees have been made, a text massage is sent from the school fees entry sheet to parent mobile phone indicating the name of the student, the class, the programme that he/she undertakes, the total school fees payable (that is the amount owe), the amount that he has currently paid and the arrears remaining.
    Counting on your usual corporation
    Thank you.
    Ebenezer Adu Amoah
    Account Officer
    Ghana

    Reply
  3. Ebenezer Adu Amoah

    Dear Sir
    Thank you very much for helping me to get a code to protect my school fees entry sheet at which at the end of the day, the account officer cannot decrease the amount paid by students. God bless you very much because the problems in the school regarding fees payment have been reduced by 60%.

    Another problem is that when receipt is issued to the students/children to be given to their parent, they lost it on their way to the house which also poses a lot of problems for the school. I therefore want you to help me in a situation where by after payment of fees have been made, a text massage is sent from the school fees entry sheet to parent mobile phone indicating the name of the student, the class, the programme that he/she undertakes, the total school fees payable (that is the amount owe), the amount that he has currently paid and the arrears remaining.
    Counting on your usual corporation
    Thank you.
    Ebenezer Adu Amoah
    Account Officer
    Ghana
    blessedyako@gmail.com
    (+233)-246-999-830

    Reply
  4. Bhavik Panchal

    Sir, What more command is required if we want to print raw one by one instead of all data in reports in shee 2?

    Reply
  5. Sharaf

    Dear
    can you check the below code are correct,

    my criteria is
    I have some data in sheet

    i need to copy the rows if the value of ComboBox1 match to colum 2

    Private Sub CommandButton1_Click()
    x = 5
    Do While Cells(x, 1) “”
    If ComboBox1.Value = Cells(x, 2) Then
    Worksheets(“Sheet2”).Rows(x).Copy
    Worksheets(“Sheet3”).Activate
    eRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets(“Sheet3”).Rows(eRow)
    End If
    Worksheets(“Sheet2”).Activate
    x = x + 1
    Loop
    End Sub

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *