Announcement

Collapse
No announcement yet.

Copy Cells and delete row

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Copy Cells and delete row



    Hello,

    I have included a 'mock-up' similar to what I need Excel to do.

    I need an efficient macro that will copy certain fields from sheet1 to specific fields in sheet2, then delete the line it currently copied from. The macro is placed on ctrl+w to give you a type of idea of how it should work (in theory).

    So it would really act like...

    Copy/Paste from Sheet1 to Sheet2
    Delete Row1
    Sheet2 gets faxed out
    Line2 Copy/Paste into Sheet2
    Delete Row2
    Repeat

    Is this possible?

    Thank you!
    Attached Files

  • #2
    Re: Auto-Populate/Delete Line Repeat

    I removed your code simply because the macro record quite often creates a lot of redundant code

    For example, you dont ned to select any cells to be able to copy it... Try this code instead:

    Code:
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+w
    '
        
        
        Do Until MsgBox("Create New Fax Sheet???", vbYesNo) = vbNo
            With Sheets("Sheet1")
                .Range("G2").Copy Destination:=Sheets("Sheet2").Range("D3")
                .Range("C2").Copy Destination:=Sheets("Sheet2").Range("H5")
                .Range("F2").Copy Destination:=Sheets("Sheet2").Range("E6")
                .Range("E2").Copy Destination:=Sheets("Sheet2").Range("E7")
                .Rows(2).Delete shift:=xlUp
                Sheets("Sheet2").Activate
                'you need to print / copy / paste here
            End With
        Loop
        
    End Sub
    Regards
    Ger
    Last edited by Ger Plante; July 31st, 2011, 23:39. Reason: clarification

    Check out our new reputation system. Click on the "star" under the post!
    _______________________________________________

    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

    Comment


    • #3
      Re: Copy Cells and delete row

      Thank you for the code, but I think I need to clarify a bit.

      We use a separate program to fax, but I'm curious if it could prompt the 'Print' screen. When I try to run the macro, it closes the entire sheet. Is this due to something in the macro?

      Thank you for any help.

      Comment


      • #4
        Re: Copy Cells and delete row

        Hi there,

        The code I provided simply copies the values from sheet 1 to sheet 2 like you asked and then deletes that row that was copied from Sheet1.... I think this is all that you asked for. I put the whole thing into a loop.

        I dont bring up any print or fax dialogs, its a simple loop. You should put your print code into (or call it from), the place in my code where I put the relevant comment.

        There is absolutely nothing in my code that would "Close the entire screen".

        So remember, all I am doing is copying the values and deleting the row in a loop, you need to control the printing/faxing the sheet when the values are copied, as per the comment in my code.

        Ger

        Check out our new reputation system. Click on the "star" under the post!
        _______________________________________________

        There are 10 types of people in the world. Those that understand Binary and those that dont.

        Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

        The BEST Lookup function of all time

        Dynamic Named Ranges are your bestest friend

        _______________________________________________

        Comment


        • #5
          Re: Copy Cells and delete row

          Ah, I needed to program the macro to the proper key. Before that happened, it simply closed my document for whatever reason.

          Do you happen to know the command to open the Print dialogue box? If I could put that in the code while removing the prompt, it would work as perfectly as I imagined.

          Comment


          • #6
            Re: Copy Cells and delete row

            like this?
            Code:
            Sub Macro2() 
                 '
                 ' Macro2 Macro
                 '
                 ' Keyboard Shortcut: Ctrl+w
                 '
                 
                 
                Do Until MsgBox("Create New Fax Sheet???", vbYesNo) = vbNo 
                    With Sheets("Sheet1") 
                        .Range("G2").Copy Destination:=Sheets("Sheet2").Range("D3") 
                        .Range("C2").Copy Destination:=Sheets("Sheet2").Range("H5") 
                        .Range("F2").Copy Destination:=Sheets("Sheet2").Range("E6") 
                        .Range("E2").Copy Destination:=Sheets("Sheet2").Range("E7") 
                        .Rows(2).Delete shift:=xlUp 
                        Sheets("Sheet2").Activate 
                         'you need to print / copy / paste here
            
            Application.Dialogs(xlDialogPrint).Show
                    End With 
                Loop 
                 
            End Sub

            Check out our new reputation system. Click on the "star" under the post!
            _______________________________________________

            There are 10 types of people in the world. Those that understand Binary and those that dont.

            Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

            The BEST Lookup function of all time

            Dynamic Named Ranges are your bestest friend

            _______________________________________________

            Comment


            • #7
              Re: Copy Cells and delete row

              That works amazingly well! I tried to remove the prompt "Create New Fax Sheet", but am at a loss as to how to keep it working without that line. Any thoughts?

              Thank you so much!

              Comment


              • #8
                Re: Copy Cells and delete row

                maybe this.... it will keep the loop going until C2 is empty (which means that all the records have been removed from Row 2.

                Code:
                Sub Macro2()
                     '
                     ' Macro2 Macro
                     '
                     ' Keyboard Shortcut: Ctrl+w
                     '
                     
                    Do Until Trim(Sheets("Sheet1").Range("C2")) = ""
                        With Sheets("Sheet1")
                            .Range("G2").Copy Destination:=Sheets("Sheet2").Range("D3")
                            .Range("C2").Copy Destination:=Sheets("Sheet2").Range("H5")
                            .Range("F2").Copy Destination:=Sheets("Sheet2").Range("E6")
                            .Range("E2").Copy Destination:=Sheets("Sheet2").Range("E7")
                            .Rows(2).Delete shift:=xlUp
                            Sheets("Sheet2").Activate
                             'you need to print / copy / paste here
                             
                            Application.Dialogs(xlDialogPrint).Show
                        End With
                    Loop
                     
                End Sub
                Ger

                Check out our new reputation system. Click on the "star" under the post!
                _______________________________________________

                There are 10 types of people in the world. Those that understand Binary and those that dont.

                Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                The BEST Lookup function of all time

                Dynamic Named Ranges are your bestest friend

                _______________________________________________

                Comment


                • #9
                  Re: Copy Cells and delete row

                  Thank you for all your help.

                  Comment


                  • #10


                    Re: Copy Cells and delete row

                    Your Welcome!

                    Call again soon

                    Ger

                    Check out our new reputation system. Click on the "star" under the post!
                    _______________________________________________

                    There are 10 types of people in the world. Those that understand Binary and those that dont.

                    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

                    The BEST Lookup function of all time

                    Dynamic Named Ranges are your bestest friend

                    _______________________________________________

                    Comment

                    Working...
                    X