pop up window to copy/paste the whole sheet from different workbook

Question

everyone. My goal is to get a pop-up window to pull a data from different workbook and copy the whole sheet from the first sheet in the pulled workbook and paste it into sheet2 of the original workbook. I need help with last line. It doesn't seem to work as I am new to VBA programming. Also, is it possible to open not only .xlsx but also .xls file type as well? One more thing, is it possible copy/paste without having to open a different file? like disabling event enabler..

   Option Explicit
    Sub test()
        Dim wb As Workbook, wb2 As Workbook
        Dim ws As Worksheet
        Dim vFile As Variant

        'Set source workbook
        Set wb = ActiveWorkbook
        'Open the target workbook
        vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
            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 targetworkbook
        Set wb2 = ActiveWorkbook

        'For instance, copy data from a range in the first workbook to another range in the other workbook
        ' wb.Worksheets(1).Cells.Copy _
        '    Destination:=newworksheet.Cells

        wb.Worksheets(2).Range("C3:D4").Value = wb2.Worksheets(1).Range("A1:B2").Value

    End Sub

To answer your `Is it possible` questions, turn on the Macro Recorder, then attempt to do what you want to do. Excel will write very poor quality code to duplicate what you've done, then you can put together pieces and fix it up so it's better quality. — FreeMan, Jun 09 '15 at 18:55

score 0 · Answer 1 · edited May 23 '17 at 10:26

I doubt if the macro recorder will be of any help in this case.

is it possible to open not only .xlsx but also .xls file type as well

Yes. Use Wildcards

vFile = Application.GetOpenFilename("Excel-files,*.xls*", 1,  _
                            "Select One File To Open", , False)

is it possible copy/paste without having to open a different file?

No. You can however retrieve values from a closed file. You may want to see THIS

I need help with last line. It doesn't seem to work as I am new to VBA programming.

I don't see anything wrong with the last line. I would however make few changes as shown below. Ensure that you are working with the correct workbooks and worksheets. If you are still getting the error then post the error message.

Sub test()
    Dim wb As Workbook, wb2 As Workbook
    Dim ws As Worksheet
    Dim vFile As Variant

    'Set source workbook
    Set wb = ActiveWorkbook '<~~ You might want to use ThisWorkbook instead

    'Open the target workbook
    vFile = Application.GetOpenFilename("Excel-files,*.xlsx", _
        1, "Select One File To Open", , False)

    If vFile = False Then Exit Sub

    'Set targetworkbook
    Set wb2 = Workbooks.Open(vFile)

    wb.Worksheets(2).Range("C3:D4").Value = wb2.Worksheets(1).Range("A1:B2").Value
End Sub

Hi, do you possibly know instead of copying the range C3:D4 it is possible to copy the entire sheet wb.worksheets2 and paste the whole sheet into wb2.worksheets(1)? — BLkrn, Jun 10 '15 at 14:55

pop up window to copy/paste the whole sheet from different workbook

1 Answers1