Copying the selected sheet to the active sheet

[jayymehta]

Hi, I just learned how to populate combo box with the sheet names of the selected workbook. Please forgive me if I ask stupid questions, as I'm new in this. Further to my project, When a file is being selected, it's sheet's names also appears in combo box. Now, what I need is that when any of these sheet is selected, that entire sheet should be copied to the NEW sheet of active workbook, renaming it ""Sheet name"Sheet". For example, if a sheet named "Input" is copied to active workbook, then a new sheet is added automatically, renaming that sheet to "InputSheet". I tried doing this by my own, but unable to think any further:

Private Sub BrowseButton_Click()  Dim fName As String
  Dim wb As Workbook
  Dim ws As Worksheet
  
  fName = Application.GetOpenFilename("Excel Files(*.xlsm),*.xlsm", , "Please select one **** excel file", , False)
  
  If fName <> "" Then
    TextBox1.Value = fName
    Set wb = Workbooks.Open(fName)
    
    For Each ws In wb.Worksheets
        ComboBox1.AddItem ws.Name
    Next
    wb.Close True
    UserForm2.ComboBox1.Text = UserForm2.ComboBox1.List(0)
    Set wb = Nothing
  Else
    MsgBox "No file is selected.", vbExclamation, "Sorry!"
  End If
End Sub


Private Sub CopySheet_Click()


    With ThisWorkbook
        .Sheets.Add(, .Sheets(.Sheets.Count)).Name = ws.Name & "Sheet"
    End With
    
End Sub

Now I don't know whether I should add the COPY CODE before adding a new sheet or after. Also, I don't know where to write the code for SELECTED sheet from combo box.(CopySheet_Click or Browse_Click). Browse_Click and CopySheet_Click are two buttons in user form for Browsing excel file from the computer and Copying the selected sheet to active workbook respectively.

 

[Norie]

Which workbook are you copying the sheet from?

If it's the workbook whose sheet names are listed in the combobox then that's kind of going to be hard because once you've populated the combobox you close that workbook.

 

[jayymehta]

Yes. I want the sheets which are populated in the combo box to be copied into the Active workbook, adding a new sheet. I think this can be done by taking a variable for the TextBox value which is the selected sheet name and then copy the sheet into active workbook. I tried doing that today and came up with the below code. But I wanted the newly formed sheet to be named after the Source sheet + Sheet. Eg: If Input Sheet is copied, then new sheet should be named "Input Sheet".

Private Sub BrowseButton_Click()  Dim fName As String
  Dim wb As Workbook
  Dim ws As Worksheet
  
  fName = Application.GetOpenFilename("Excel Files(*.xlsm),*.xlsm", , "Please select one **** excel file", , False)
  
  If fName <> "" Then
    TextBox1.Value = fName
    Set wb = Workbooks.Open(fName)
    
    For Each ws In wb.Worksheets
        ComboBox1.AddItem ws.Name
    Next
    wb.Close True
    UserForm2.ComboBox1.Text = UserForm2.ComboBox1.List(0)
    Set wb = Nothing
  Else
    MsgBox "No file is selected.", vbExclamation, "Sorry!"
  End If
End Sub


Private Sub CopySheet_Click()


  Dim fName As String
  Dim sName As String
  Dim wbSource As Workbook
  Dim wbTarget As Workbook
  Dim ws As Worksheet


  Set wbTarget = ThisWorkbook
  fName = UserForm2.TextBox1.Value
  With UserForm2.ComboBox1
    If .ListIndex >= 0 Then
        sName = .Text
        Set wbSource = Workbooks.Open(fName)
        wbSource.Sheets(sName).Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)
        wbSource.Close True
    End If
  End With
    
End Sub

 

[Norie]

Add code to rename the copied sheet.

wbTarget.Sheets(wbTarget.Sheets.Count).Name = sName & "Sheet"

 

[jayymehta]

Yes, got it! Sir, I'm getting multiple problems in my code.
When I have already filtered .xlsm files to appear, still some Text files, PDFs also appear in Dialog box when searching *.* And by selecting it, it also gets selected and shows in the TextBox of UserForm.
When Selecting a file after browsing, it has 3 sheets; now before resetting the form, when I'm again selecting that same file, the combo box has now 6 sheets populated; each sheet got duplicated.
When the sheet is copied, and after some changes in original sheet when I'm again copying it, it shows an error "The file name already exists", which I want it to get it replaced. Below is my code after some changes:

Private Sub BrowseButton_Click()

  Dim fName As String
  Dim wb As Workbook
  Dim ws As Worksheet
  
  fName = Application.GetOpenFilename("Excel Files(*.xlsx),*.xlsx", , "Please select one **** excel file", , False)
  
  If fName = "" Or fName = "False" Then
  Exit Sub
  Else
    TextBox1.Value = fName
    Set wb = Workbooks.Open(fName)
    
    For Each ws In wb.Worksheets
        ComboBox1.AddItem ws.Name
    Next
    wb.Close True
    UserForm2.ComboBox1.Text = UserForm2.ComboBox1.List(0)
    Set wb = Nothing
  End If
End Sub


Private Sub CopySheet_Click()


  Dim fName As String
  Dim sName As String
  Dim srcWbk As Workbook
  Dim tgtWbk As Workbook
  Dim ws As Worksheet


  Set tgtWbk = ThisWorkbook
  
  fName = UserForm2.TextBox1.Value
  
  With UserForm2.ComboBox1
    If .ListIndex >= 0 Then
        sName = .Text
        Set srcWbk = Workbooks.Open(fName)
        srcWbk.Sheets(sName).Copy After:=tgtWbk.Sheets(tgtWbk.Sheets.Count)
        ActiveSheet.Name = sName & " Sheet"
        
        srcWbk.Close True
    End If
  End With
    
End Sub

 

[Norie]

Not sure about the first problem, as far as I can see you should only get Excel workbooks in the dialog box.

The second problem is easily solved, clear the combobox before adding the sheet names to it.

ComboBox1.Clear

Not sure exactly what the third problem is, what exactly are you doing after you copy a sheet?

 

[jayymehta]

The third problem is, for eg, If I copy "Input" sheet to active workbook, it creates a sheet in active wbk named "Input Sheet". Now I made some changes in original "Input" sheet, added some values or deleted some cells, etc... After that when I'm again copying it to active workbook, it doesn't get copied. It should replace the Previously copied file. Instead it shows an error of "File name already exists".
The first problem is, When I click on Browse button, it only shows Excel files as expected. Now, in Search box, besides the File type box, If i type *.* it shows all the types of files, but in Hidden format. That is still fine, but the problem arises when I select that Hidden file, and it shows the result in combo box and full path is also displayed. Instead it should deny to select such file types with msgbox "You have selected incorrect file type". Pls tell me if you didn't understood it yet.

 

[Norie]

Still not sure about that third problem, will you be copying the sheet back into the workbook it was originally copied from?

As for the dialog, why would you type *.* and why would you select a 'hidden' file?

 

[jayymehta]

You guessed the third problem right. I will be copying the sheet back into the wbk it was originally copied from. As the name would be same while copying it, it shows an error of "File name already exists".
And for the dialog, of course I won't type *.*, but I was just finding possibilities of getting an error before submitting the project.