Results 1 to 9 of 9

Opening file, copy and paste range

Threaded View

  1. #1
    Registered User
    Join Date
    05-28-2019
    Location
    Indianapolis
    MS-Off Ver
    Excel 2016
    Posts
    12

    Opening file, copy and paste range

    Hello everyone,
    I'm sure this is a very basic question, but I'm new at this. I'm trying to prompt the user to select an Excel workbook, then copy and paste a range of cells from that workbook into the active one. This is my attempt below:
    Sub CommandButton1_Click()
    
    
    Dim WS As Worksheet
    
    
    Dim wb As Workbook, wb2 As Workbook
    Dim vFile As Variant
    
    'Set source workbook
    Set wb = ActiveWorkbook
    
    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xls*", _
        1, "Select One File To Open", , False)
    
    'if the user didn't select a file, exit sub
    If TypeName(vFile) = "Boolean" Then Exit Sub
    Workbooks.Open vFile
    
    'Set selectedworkbook
    Set wb2 = ActiveWorkbook
    
    'Select cells to copy
    wb2.Worksheets("Output").Range("A10:C15").Select
    Selection.Copy
    
    'Go back to original workbook you want to paste into
    wb.Activate
    
    'Paste starting at the last empty row
    wb.Worksheets("Master").Range("A10:C15").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    'Close and save the workbook you copied from
    wb2.Save
    wb2.Close
    
    End Sub
    The dialog box successfully opens, and the selected file successfully opens, and then I get a run-time error 91 on the following line:
    y.Sheets("Autoreport1.xlsm").Range("A10:C14").Value = x.Sheets("strFileToOpen").Range("A10:C14")
    Again, this is probably something really stupid but all the same I need help. Thanks!
    Last edited by alansidman; 05-28-2019 at 08:14 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copy range and paste in a new excel file.
    By Vagelisr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2018, 07:31 AM
  2. Copy Visible Range in Excel and Paste into Text File
    By jpv5 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-14-2016, 09:15 AM
  3. [SOLVED] Copy same range in many excel files and paste into a main file
    By dualaudio454252 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-15-2014, 05:25 PM
  4. macro to copy used range and paste in the body tag of a html file/text file
    By amethystfeb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-24-2014, 08:34 PM
  5. Macro required for copy a range, create a new txt file and paste it
    By gm2612 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-28-2013, 07:25 AM
  6. Code to copy range from 200 + workbooks and paste into one csv import file
    By CharlieMappley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-14-2013, 10:41 AM
  7. Copy and paste without opening excel file
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-20-2010, 10:14 AM

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