Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Need macro for copying range of cells from worksheet to master workbook

    Hi everyone,
    I am new to vba for Excel. I am trying to create a feedback form in excel, when a button is pressed the form should then be copied and pasted in to a separate workbook and all of the input data is then deleted.

    I have managed to copy the form to another sheet within the workbook when the button is pressed using the code below but I am struggling to copy it over to a separate workbook.
    Code:
    Private Sub CommandButton3_Click()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    
    
    Set copySheet = Worksheets("Sheet1")
    Set pasteSheet = Worksheets("Sheet3")
    
    
    copySheet.Range("B7:H17").Copy
    pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    Range("D8").ClearContents
    Range("F8:H8").ClearContents
    Range("B11:H16").ClearContents
    
    End Sub
    Thanks in advance for your responses
    Last edited by RetiredGeek; 2015-05-04 at 13:16. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Freddos,

    Welcome to the Lounge as a new poster!

    This code should do what you want:
    Code:
    Option Explicit
    
    Sub TransferValues()
    
       Dim shtCopyFrom As Worksheet
       Dim shtPasteTo  As Worksheet
       Dim wkbSource   As Workbook
       Dim wkbDest     As Workbook
    
       Application.ScreenUpdating = False
    
       Set wkbSource = ActiveWorkbook
       '*** Replace d:\path\filename.ext below ***
       Set wkbDest = Application.Workbooks.Open(Filename:="G:\BEKDocs\Excel\Test\Freddos2.xlsx")
    
       Set shtCopyFrom = wkbSource.Worksheets("Sheet1")
       Set shtPasteTo = wkbDest.Worksheets("Sheet3")
    
       shtCopyFrom.Range("B7:H17").Copy
       shtPasteTo.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
    
       Application.CutCopyMode = False
       Application.ScreenUpdating = True
       
       wkbDest.Close True
    
       Range("B7:H17").ClearContents
    
    End Sub  'TransferValues
    Just place this code in a standard module and then assign it to your button. Remember to change the destination drive/path/filename.ext in the code.

    Here are my Test files:
    Freddos.xlsm
    Freddos2.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Freddos (2015-05-06)

  4. #3
    New Lounger
    Join Date
    May 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    RetiredGeek,

    Cheers for that, it worked perfectly, though I have encountered one more problem along the way. A couple of the cells I am copying over use data validation in the form of a dropdown list. Theses to stop the macro from working.
    Any ideas of if it is possible to get around this??

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Freddos,

    I assume you only want the VALUES copied not the validation? If so just modify this one line:

    From: shtPasteTo.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial

    To: shtPasteTo.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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