VBA Code to Select/Import Worksheet From a Workbook

[Fieldsdj]

I need help excel gurus...

Here is my situation: I have a workbook called "AFE MTP_4SOS.xlsm" and one called "Export.xlsm". I need a macro that will run from "AFE MTP_4SOS" and import a worksheet from "Export". So the macro will need to open "Export" and show a list of all the available worksheets and let the user choose which one to import. Once the user chooses, that worksheet should then be imported after the last tab in "AFE MTP_4SOS" and then deleted from the "Export" workbook.

Thank you in advance for your assistance.

 

[cerfani]

you can make a userform that adds checkbox controls dynamically by creating a checkbox for each sheet in Export. Then in an ok button event you run code that looks at which checkboxes are checked and then copying and moving sheets from one workbook to another is a matter of referencing one workbook's sheet and moving to another workbook reference.

Do you know how to write VBA because your question is vague. If you wanted to know how to do it then my suggestion is one of many ways to accomplish.

If you need the code I can help you but can you write code if i explain it?

 

[Fieldsdj]

Thanks for the quick reply cerfani. I have very little experience with writing code and I've never seen a userform before. If you want to try and explain it to me I'll do my best to try and follow directions.

 

[cerfani]

Thanks for the quick reply cerfani. I have very little experience with writing code and I've never seen a userform before. If you want to try and explain it to me I'll do my best to try and follow directions.

Well I want to let you know now, that in order to accomplish this you will have lots of reading to do but none of the concepts are difficult but you do have to be aware of many things so it can be hard at first. Start with this...

Excel Forms (Userforms) (how to create a form)

I suggest googling "how to make simple vba user form"

Creating Controls at Runtime, On the Fly. Excel Training VBA 2 lesson 21 (adding controls to a form dynamically)

I will make some example for you.

 

[cerfani]

btw if you want something a little more rough, you can accomplish with an inputbox and not use a userform but a userform will be way better

i will make an inputbox version

 

[cerfani]

Sub SheetImport()
'this code is in a module in AFE MTP_4SOS.xlsm
' call this once Export.xlsm is open or else you gotta write code to open it
'i didnt bother to declare some integers or longs


    Dim msg As String
    msg = ""
    
    With Workbooks("Export.xlsm")
        For i = 1 To .Worksheets.Count
            msg = msg & "(" & i & ") " & .Worksheets(i).Name & vbCrLf
        Next i
    
        response = InputBox(msg, "Type numbers for sheets to import")
    
        If response = Null Then Exit Sub 'check for cancel button
    
        For x = 1 To Workbooks("Export.xlsm").Worksheets.Count
            If InStr(response, x) > 0 Then
                Dim ws As Worksheet
                Set ws = Workbooks("Export.xlsm").Worksheets(x)
                ws.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'you can copy to and not move
            End If
        Next x
    End With
End Sub

 

[cerfani]

edited a couple times, make sure you refresh page... forgot i used the with in the 2nd loop... doh

 

[cerfani]

make sure you loop backwards in 2nd loop, i will edit code

 

[cerfani]

Sub SheetImport()
'this code is in a module in AFE MTP_4SOS.xlsm
' call this once Export.xlsm is open or else you gotta write code to open it
'i didnt bother to declare some integers or longs


    Dim msg As String
    msg = ""
    
    With Workbooks("Export.xlsm")
        For i = 1 To .Worksheets.Count
            msg = msg & "(" & i & ") " & .Worksheets(i).Name & vbCrLf
        Next i
    
        response = InputBox(msg, "Type numbers for sheets to import")
    
        If response = Null Then Exit Sub 'check for cancel button
    
        For x = .Worksheets.Count To 1 Step -1
            If InStr(response, x) > 0 Then
                Dim ws As Worksheet
                Set ws = .Worksheets(x)
                ws.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'you can copy to and not move
            End If
        Next x
    End With
End Sub

So instead of making a fancy userform with checkboxes you select your sheets by typing the corresponding sheet index

for example if you typed "135" or 1.5.3" or whatever, it would move those sheet indexes from export to the workbook calling code

 

[Fieldsdj]

Sub SheetImport()
'this code is in a module in AFE MTP_4SOS.xlsm
' call this once Export.xlsm is open or else you gotta write code to open it
'i didnt bother to declare some integers or longs


    Dim msg As String
    msg = ""
    
    With Workbooks("Export.xlsm")
        For i = 1 To .Worksheets.Count
            msg = msg & "(" & i & ") " & .Worksheets(i).Name & vbCrLf
        Next i
    
        response = InputBox(msg, "Type numbers for sheets to import")
    
        If response = Null Then Exit Sub 'check for cancel button
    
        For x = .Worksheets.Count To 1 Step -1
            If InStr(response, x) > 0 Then
                Dim ws As Worksheet
                Set ws = .Worksheets(x)
                ws.Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count) 'you can copy to and not move
            End If
        Next x
    End With
End Sub

So instead of making a fancy userform with checkboxes you select your sheets by typing the corresponding sheet index

for example if you typed "135" or 1.5.3" or whatever, it would move those sheet indexes from export to the workbook calling code

That code works perfectly. I read some of the material on the userform and it sounds pretty simple to make one but to program it seems pretty complicated. I'll play around with it and see if I can figure it out. Thanks a bunch for your help!!