Open file and copy paste

[vmjan02]

Is there a way were user can select the file and once the file is selected then it will copy and paste the same in another file

I have this code and it does copy past. But need to modify it so that user can select the file and then will copy paste. Any suggestion

'=====================================================================================
    ' 1. Check Raw data in folder sheet MSP Response Time With Hubtime
'=====================================================================================
    If sheets("Reference").Range("c2").Value <> "MSP Response Time With Hubtime TTs" Then
    MsgBox "MSP Response Time With Hubtime TTs Raw data file not avaliable" & vbNewLine & "Download raw file & Run the scrip again", vbCritical, "Raw File Required"
    End
    End If
 
    If Len(Dir(Fpath & "\" & "MSP Response Time With Hubtime TTs.xlsx")) = 0 Then
    MsgBox "_MSP_Response_Time_With_Hubtime_TTs_v1.0(1) in the folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
    End
    End If

'=====================================================================================
    ' 1. ImportMSP Response Time With Hubtime  MSP Response Time With Hubtime TTs.xlsx
'=====================================================================================
       
    If sheets("Reference").Range("c2").Value = "MSP Response Time With Hubtime TTs" Then
   
    sheets("MSP Response Time RawData").Visible = True
    sheets("MSP Response Time RawData").Unprotect "etmc123$"
    Windows("TicketExceeds24hours.xlsm").Activate
    ActiveWorkbook.sheets("MSP Response Time RawData").Activate
    Cells.Select
    Selection.ClearContents
    Application.CutCopyMode = False
       
    Set wbsource = Workbooks.Open(Fpath & "\" & "MSP Response Time With Hubtime TTs.xlsx")
           
    Windows("MSP Response Time With Hubtime TTs.xlsx").Activate
        ActiveSheet.Range("B8:BA200000").Select
        'Cells.Select
        Selection.Copy
       Windows("TicketExceeds24hours.xlsm").Activate
        ActiveWorkbook.sheets("MSP Response Time RawData").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If


'=====================================================================================
    ' 2. Check Raw data in folder sheet Summary Of PR Detailed Handled By
'=====================================================================================
    If sheets("Reference").Range("c3").Value <> "Summary Of PR Detailed Handled By" Then
    MsgBox "Summary Of PR Detailed Handled By Raw data file not avaliable" & vbNewLine & "Download raw file & Run the scrip again", vbCritical, "Raw File Required"
    End
    End If
 
    If Len(Dir(Fpath & "\" & "Summary Of PR Detailed Handled By.xlsx")) = 0 Then
    MsgBox "Summary Of PR Detailed Handled By in the folder" & vbNewLine & Fpath & vbNewLine & "Download raw file & Run the scrip again"
    End
    End If

'=====================================================================================
    ' 2. ImportSummary Of PR Detailed RawData  Summary Of PR Detailed RawData.xlsx
'=====================================================================================
       
    If sheets("Reference").Range("c3").Value = "Summary Of PR Detailed Handled By" Then
   
    sheets("Summary Of PR Detailed RawData").Visible = True
    sheets("Summary Of PR Detailed RawData").Unprotect "etmc123$"

    Windows("TicketExceeds24hours.xlsm").Activate
    ActiveWorkbook.sheets("Summary Of PR Detailed RawData").Activate
    Cells.Select
    Selection.ClearContents
    Application.CutCopyMode = False
       
    Set wbsource = Workbooks.Open(Fpath & "\" & "Summary Of PR Detailed Handled By.xlsx")
           
    Windows("Summary Of PR Detailed Handled By.xlsx").Activate
        ActiveSheet.Range("B8:BA200000").Select
        'Cells.Select
        Selection.Copy
       Windows("TicketExceeds24hours.xlsm").Activate
        ActiveWorkbook.sheets("Summary Of PR Detailed RawData").Activate
        ActiveSheet.Paste
        Application.CutCopyMode = False
    wbsource.Close
    End If

 

[yinkajewole]

this will allow the user to select the file
<

Dim myFile as String
With Application.FileDialog(msoFileDialogFilePicker)
    .Show
    myFile = .SelectedItems(1)
End With
'myFile will be the variable for the file selected

 

[vmjan02]

Dim myFile as String With Application.FileDialog(msoFileDialogFilePicker) .Show myFile = .SelectedItems(1) End With 'myFile will be the variable for the file selected

It is not allowing to open the file

 

[yinkajewole]

It is not allowing to open the file

how do you mean? Does it not show up a dialog box to select the file or the file selected is not showing?

 

[vmjan02]

I mean it allows to select the file.
But after selection it should open the file. That is not working.

 

[yinkajewole]

can I see your full code?

 

[TotallyConfused]

Hello Vmjan02

Is this what you are looking for?

Sub GetAndOpenFile()
   Dim fPath As String
   Dim fName As String
   Dim WB as Workbook
        fPath = "ThisWorkbook.Path\"  
        fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsm)," & fPath & "*.xlxs")     ' Will list any Excel file in that path with the extension of 'xlsm' or 'xlxs'.
        Set WB = Workbooks.Open(fName)
End Sub

I hope this helps.
TotallyConfused

 

[vmjan02]

Dim fPath As String Dim fName As String Dim WB as Workbook fPath = "ThisWorkbook.Path\" fName = Application.GetOpenFilename("Excel Files (" & fPath & "*.xlsm)," & fPath & "*.xlxs") ' Will list any Excel file in that path with the extension of 'xlsm' or 'xlxs'. Set WB = Workbooks.Open(fName)

Yes. This is what i was looking for. Thanks a ton

 

[TotallyConfused]

You're very welcome. Glad I could help and thanks for the feedback.