How do I Copy a Worksheet from one Workbook to Another

Question

I want to Copy one Sheet from a Workbook I'm opening via a FilePath into another Workbook that contains the Macro I'm running.

What I'm having trouble with is that Whenever I copy and Paste the files the Macro creates a new Workbook and pastes the data in the first sheet in that workbook. I was defining a specific sheet within the second workbook to paste the code so I'm unsure why the destination of my paste is a random workbook.


Public filepath As String

Sub FileOpenDialogBox()

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in fullpath variable
  With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        'Show the dialog box
        .Show

        'Store in fullpath variable
        fullPath = .SelectedItems.Item(1)
    End With
    filepath = fullPath
    'It's a good idea to still check if the file type selected is accurate.
    'Quit the procedure if the user didn't select the type of file we need.
    If InStr(fullPath, ".xls") = 0 Then
        Exit Sub
    End If

    'Open the file selected by the user
    'Workbooks.Open fullpath

End Sub

Sub CopySheet()
    'Module 1 FilePath import as Variable
    MsgBox filepath

    Dim spo_book As Workbook
    Dim target_book As Workbook
    Set spo_book = ActiveWorkbook
    Set target_book = Workbooks.Open(filepath)

    Dim dst_sheet As Worksheet
    Dim target_sheet As Worksheet
    Set dst_sheet = spo_book.Sheets("SPO Data")
    Set target_sheet = target_book.Sheets("Untimed Parts")
    target_sheet.Copy
    dst_sheet.Paste

End Sub

The expected outcome is that the Copy-Paste will Copy from the Chosen Workbook via my FileDialog into the Sheet called "SPO DATA' that I set as the variable dst_sheet, I thought it might've been a range issue and I tried to put in a range and it said the data didn't match so I went back to my sheet paste.

@urdearboy I have, but I've seen a lot of cases of people defining both workbooks with Paths but this Macro is going tobe distributed to others and defining a Path isn't a viable solution as not all of them would be capable of editing a Macro to make it work for them. I tried only defining the book i want to copy from but even that doesn't work. — Rob, Aug 30 '19 at 16:17
How to do this is also well documented here. The entirety of your problem (in segments) exists here. Search for the questions to individual components of your problem. Moving worksheets has nothing to do with dialogue pickers - search these topics individually and combine the solutions together. By not doing this, you are essentially asking others to do your work project for you. https://stackoverflow.com/questions/26392482/vba-excel-to-prompt-user-response-to-select-folder-and-return-the-path-as-string — urdearboy, Aug 30 '19 at 16:19
Possibly not the prettiest, but gets the path, moves sheet, and then saves as... If you don't save changes on the source then move is effectively copy... See https://stackoverflow.com/a/30605765/4961700 — Solar Mike, Aug 30 '19 at 18:43
I've Editted the Question to be more descriptive and refined it to make it more narrow. I've made some progress in some areas so that question should just be on the Entirety of my issues with Copying and Pasting between Workbooks.. — Rob, Sep 03 '19 at 17:41

Thomas Ludewig · Accepted Answer · 2019-09-03T18:17:10.253

1

How about do it a bit old school :

    sub sample()

    Application.ScreenUpdating = false
    Sheet1="Willy"
    Sheet2="Wilma"

    for row1=20 to 500
    for col= 30 to 3300

    Sheets(Sheet1).Cells(row1, col1).Value=Sheets(Sheet2).Cells(row2, col2).Value
    Sheets(Sheet1).Cells(row1, col1).Formula=Sheets(Sheet2).Cells(row2, col2).Formula
    Sheets(Sheet1).Cells(row1, col1).Comment=Sheets(Sheet2).Cells(row2, col2).Comment

    next
    next
    Application.ScreenUpdating = True
    end sub

'cause somebody has asked how to address some sheets :)

  Sub ws_all()
  Dim wb As Workbook

For Each wb In Application.Workbooks
    Debug.Print wb.Name

    For Each ws In wb.Sheets

        Debug.Print ws.Name

    Next
Next

Debug.Print Application.Workbooks; ("Workbookname").Sheets  ("Sheetname").Name

End Sub

edited Sep 03 '19 at 18:17

answered Aug 30 '19 at 19:53

Thomas Ludewig

696
9
17

This looks good, but This works more for copying one Sheets to another if I understand this entirely. I understand that form of Copying but my question pertains to Workbook to Workbook Copying of a Sheet. – Rob Sep 03 '19 at 17:39
Then you just have to look at the open workbooks as well. Its the same thing. Just at top of all the workbook as the master object. I add a sample. – Thomas Ludewig Sep 04 '19 at 16:41

How do I Copy a Worksheet from one Workbook to Another

1 Answers1

Linked