Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Copy Cells and delete row

  1. #1
    Join Date
    17th June 2011
    Posts
    238

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    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:

    VB:
    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 at 23:39. Reason: clarification
    _______________________________________________
    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

    _______________________________________________

  3. #3
    Join Date
    17th June 2011
    Posts
    238

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    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
    _______________________________________________
    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

    _______________________________________________

  5. #5
    Join Date
    17th June 2011
    Posts
    238

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    Re: Copy Cells and delete row

    like this?
    VB:
    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 
    
    
    _______________________________________________
    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

    _______________________________________________

  7. #7
    Join Date
    17th June 2011
    Posts
    238

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    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.

    VB:
    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
    _______________________________________________
    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

    _______________________________________________

  9. #9
    Join Date
    17th June 2011
    Posts
    238

    Re: Copy Cells and delete row

    Thank you for all your help.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,540

    Re: Copy Cells and delete row

    Your Welcome!

    Call again soon

    Ger
    _______________________________________________
    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

    _______________________________________________

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: July 29th, 2011, 08:50
  2. Replies: 2
    Last Post: May 24th, 2011, 08:32
  3. Replies: 6
    Last Post: May 19th, 2011, 02:38
  4. delete empty cells & zero value cells
    By abdi1 in forum EXCEL HELP
    Replies: 6
    Last Post: September 22nd, 2010, 04:36
  5. Replies: 5
    Last Post: August 13th, 2010, 15:56

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno