CODEWITHSUNDEEP
vbaexcelbutton
dre
dre

Reputation: 183

User Selection (workbook and sheet name) then Copy and Paste

Is there a way to make it where user selects which sheet to copy from, after they opened file?

I'd like to do this because there may be multiple sheets with same format, but have different names.

Example: Original workbook named VSC (Contains sheets Compare, Plot)

Secondary workbook named SF (Contains sheets Results1, Results2, Results3)

User clicks button on VSC, and file dialog opens, and user selects SF in a certain directory, then asks user to select which sheet to choose from - user chooses Results2 sheet, copies the data (Range"B2:B5"), then pastes it back to Compare sheet.

Would this be possible? I am not sure how to start.

Current code that asks user to select SF workbook:

Sub GetFilePath()
Dim objFSO as New FileSystemObject

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
If .Show <> -1 Then
    Exit Sub
End If
FileSelected = .SelectedItems(1)
End With

Upvotes: 1

Views: 1256

Answers (3)

SJR
SJR

Reputation: 23081

Here is one approach. You type in the sheet name. Adjust copy and paste ranges to suit.

Sub GetFilePath()

Dim objFSO As New FileSystemObject, w As String, wb As Workbook

Application.ScreenUpdating = False

Set myFile = Application.FileDialog(msoFileDialogOpen)
With myFile
    .Title = "Choose File"
    .AllowMultiSelect = False
    If .Show <> -1 Then
        Exit Sub
    End If
    Set wb = Workbooks.Open(.SelectedItems(1))
End With

w = InputBox("Enter sheet name")

If SheetExists(w) Then
    wb.Sheets(w).Range("B2:B5").Copy
    ThisWorkbook.Sheets("Compare").Range("A1").pastespecial xlvalues
Else
    MsgBox "Sheet not found"
End If

wb.Close False

Application.ScreenUpdating = True

End Sub

Function SheetExists(s As String) As Boolean

Dim x    
On Error GoTo NextSheet
x = ActiveWorkbook.Sheets(SheetName).Name
SheetExists = True
Exit Function    
NextSheet:
    SheetExists = False    
End Function

Upvotes: 1

Ricardo A
Ricardo A

Reputation: 1815

Here is a different approach to your question. It is using a Timer after opening the new Workbook to ask you every 10 seconds if you are on the sheet you want to copy from. If you answer "Yes" it will copy. If you answer "no" it will restart the 10 second timer.

Sub GetFilePath()
    Set MyFile = Application.FileDialog(msoFileDialogOpen)
    With MyFile
        .Title = "Choose File"
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        FileSelected = .SelectedItems(1)
    End With
    Set newWk = Workbooks.Open(FileSelected, , True)
    'Open Selected Workbook and check in 10 seconds for Selected Sheet Name
    Application.OnTime Now + TimeValue("00:00:10"), "CheckForSheet"
End Sub
Private Sub CheckForSheet()
    Dim SheetName As String
    SheetName = ActiveSheet.Name
    answer = MsgBox("Is This the Sheet to copy from: " & SheetName & "?", vbYesNo + vbQuestion, "Copy Data?")
    If answer = vbYes Then
        'ThisWorkbook is the workbook with the Macro/VBA code
        'ActiveWorkbook is the workbook where you are selecting the Sheet to copy from
        ActiveWorkbook.Sheets(SheetName).Range("B2:B5").Copy
        ThisWorkbook.Sheets("Compare").Range("C1:C4").PasteSpecial
    Else
        'Check Again in 10 Seconds
        Application.OnTime Now + TimeValue("00:00:10"), "CheckForSheet"
    End If
End Sub

Upvotes: 1

Hrothgar
Hrothgar

Reputation: 422

Once you have your workbook open, you can enumerate the sheets by name and populate a pick list on a sheet in the VSC workbook...

Upvotes: 1

Related Questions