+ Reply to Thread
Results 1 to 3 of 3

Macro to Copy a range from one sheet and sellect where to paste in another sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    15

    Macro to Copy a range from one sheet and sellect where to paste in another sheet

    Hello all,
    I've been working on this for some time now and just can't figure it out.
    This is what I have so far. Can't seem to find the right routine for a sellection message box or a positive check for a cell with data and move to next selection routine.
    I have attached an example of the sheet I am using with Macro1 already run.

    Here is my current code that pastes to all four choices.

    Sub Macro2()
    '
    ' Macro2 Macro
    ' Copies Data range from "Calculation" to "Next" in selected line and delets data in "Import"
    ' Keyboard Shortcut: Ctrl+C
    '
    'Could use a selection Form or mesagebox with Day 1 through Day 4 buttons here to determine where to paste selected data.
    'Or macro would have to determine if target cells are full and move to the next target then stop if target is available and
    'paste was successful.
    
        Sheets("Calculation").Select        'Selects the "Calculation" Sheet
        Range("C4:I4").Select               'Selects the range to copy
        Selection.Copy                      'Copies the range
        Sheets("Next").Select               'Selects the "Next" Sheet
        Range("B18").Select                 'Selects the starting cell for paste.
                                            'Need to be able to select the next range if B18 is already full or stop sub if paste ocures below
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False       'Paste Special comand
        Range("B13").Select                 'Returns Focus to B13
            
    
    
        Range("B27").Select                 'Selects the starting cell for paste
                                            'Need to be able to select the next range if B27 is already full or stop sub if paste ocures below
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False       'Paste Special comand
        Range("B22").Select                 'Returns Focus to B22
    
        
        Range("B36").Select                 'Selects the starting cell for paste
                                            'Need to be able to select the next range if B36 is already full or stop sub if paste ocures below
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False       'Paste Special Command
        Range("B31").Select                 'Returns Focus to B31
    
    
        Range("B45").Select                  'Selects the starting cell for paste
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False        'Paste Special Command
        Range("B40").Select                  'Returns Focus to B40
    'Should be the last available sellection, Need to possibly rename current "Next" sheet and make a new "Next" sheet if B45 is Full
      
    
        Sheets("Calculation").Select        'Selects "Calculation" Sheet
        Range("A1").Select                  'Focuses on Cell A1
        
        'Sheets("Import").Select             'Selects "Import" Sheet
        'Cells.Select                        'Selects all Cells
        'Selection.ClearContents             'Erases all data from "Import" sheet to avoid duplication
        'Range("A1").Select                  'Focuses on cell A1
        Sheets("Next").Select               'Returns to "Next" sheet at macro conclusion
        
    End Sub
    Thanks
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-07-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Macro to Copy a range from one sheet and sellect where to paste in another sheet

    Well I was able to accomplish my goal with 4 macros and 4 selection buttons.
    See the attached Example.
    I would still like to accomplish this with a selection mesagebox routine or a single button / macro seeing the first destination full and moving to the second and so on till it can paste values.
    Attached Files Attached Files
    Last edited by Bud Wilkinsonn; 01-23-2013 at 01:37 AM.

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    United States
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Macro to Copy a range from one sheet and sellect where to paste in another sheet

    So I have done some work on this and found a reasonable Dropdown Box Solution to select the apropriate macro to run.

    The only problem is that with the dropdown box located on the "Blank" sheet, the dropdown box name changes on the "Next" sheet when "NewNext" macro is run and renaming the first "Next" sheet and adding another "Next" sheet then requires changing the "Dropdown Box 2" identifier in the "DropDown_Change" Macro.

    I supose I could move the New Next Sheet Button and the Macro Selection Dropdown Box to a different sheet and fix that problem. The 5 button system worked on the copied sheet without problem too.

    I have attached the revision and the code should be in the VB of the workbook.

    ***Edit***

    I ended up just moving the New Next Sheet button and the Drop Down box to the Calculation sheet and will use the workbook that way till a more elegant method comes about.
    Attached Files Attached Files
    Last edited by Bud Wilkinsonn; 01-25-2013 at 04:35 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1