Method to Move and Copy Sheets from Other Workbooks VBA

[austinandrei]

Need help on a method that will let user select a worksheet from another workbook and move and copy that sheet to the main workbook containing the macro. Note that the workbook is already opened thus activated already and there is no specific name of the workbook as it might have different filenames, so the user gets total control on what workbook and what worksheet to activate. Inputbox and RefEdit control of Userform is not functioning well for this kind of situation. I really need a work around on this, please. Thanks in advance!

 

[Fluff]

This will copy the activesheet to the workbook containing the code

Sub MoveSht()
   ActiveSheet.Copy ThisWorkbook.Sheets(1)
End Sub

 

[austinandrei]

Hi Fluff, thanks for the reply. The active worksheet initially is the one containing the macro. Then what will happen is that the user will run a macro code that will let him go to a specific worksheet of another active workbook (which is not defined) and move and copy that sheet to that main workbook (the workbook with macro).

So basically I have this Workbook 1 with macro then it will let the user choose from any other active workbooks and copy the specific sheet within it back to Workbook 1.

 

[Fluff]

The easiest option would be to get the user to select the sheet they want to copy & then run the macro.
Creating code to allow somebody to select an open workbook & then select a sheet within it, whilst the macro is running, is not simple.

 

[austinandrei]

The reason it will need a macro is because there will be a lot of sheets to copy which is 1 worksheet per one workbook. As far as I remember, I was successful before using inputbox for a macro i created years ago for a certain task but itwas a lower version of excel, I am not sure why on a higher excel version, it is not functioning. Thus I really need an alternative.

 

[Fluff]

What is you existing macro?

 

[austinandrei]

I am no longer sure of that as I made it on my previous company. This is for a new process I am doing and I cannot use inputbox for this function. Not even RefEdit control.

 

[Fluff]

Create a userform with 2 comboboxes & a commandbutton & try this

Option Explicit
Private Dic As Object

Private Sub ComboBox1_Change()
Me.ComboBox2.Clear
Me.ComboBox2.List = Dic(Me.ComboBox1.Value).keys
End Sub

Private Sub CommandButton1_Click()
Workbooks(Me.ComboBox1.Value).Sheets(Me.ComboBox2.Value).Copy ThisWorkbook.Sheets(1)
End Sub

Private Sub UserForm_Initialize()
   Dim Wbk As Workbook
   Dim Ws As Worksheet
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.CompareMode = vbTextCompare
   For Each Wbk In Workbooks
      If Not Wbk.Name = ThisWorkbook.Name And Not Wbk.Name = "PERSONAL.XLSB" Then
         Dic.Add Wbk.Name, CreateObject("scripting.dictionary")
         For Each Ws In Wbk.Worksheets
            Dic(Wbk.Name).Add Ws.Name, Nothing
         Next Ws
      End If
   Next Wbk
   Me.ComboBox1.List = Dic.keys
End Sub

 

[austinandrei]

Hi Fluff, sorry was in a hospital for a couple of days, thus just read your reply. Anyway, that was a fantastic alternative Thanks a lot for the help!

 

[Fluff]

Glad to help & thanks for the feedback