Macro that will transfer selected sheets from selected .xlsm workbook to my active .xlsm workbook

[MonsterVG]

Hi guys,

I'm very new to the VBA. Hope someone can help. I created the code below to transfer sheets that I selected from another workbook to my active workbook. However I kept receiving error below. Is there someone that can help? Thank you so much!

Sub TransferSelectedSheets()
Dim sourceWB As Workbook
Dim targetWB As Workbook
Dim selectedSheets As Variant
Dim i As Integer

' Select the source workbook
Dim sourcePath As Variant
sourcePath = Application.GetOpenFilename("Excel Files (*.xlsm*), *.xlsm*")

If TypeName(sourcePath) = "Boolean" Then
MsgBox "No source workbook selected. Macro aborted.", vbExclamation
Exit Sub
End If

Set sourceWB = Workbooks.Open(sourcePath)

' Select the sheets to transfer
On Error Resume Next
selectedSheets = Application.InputBox("Select the sheets to transfer", Type:=8)

If VarType(selectedSheets) = vbBoolean Then
MsgBox "No sheets selected. Macro aborted.", vbExclamation
sourceWB.Close SaveChanges:=False
Exit Sub
End If

' Create a temporary workbook to hold the selected sheets
Dim tempWB As Workbook
Set tempWB = Workbooks.Add

' Copy the selected sheets to the temporary workbook
For i = 1 To UBound(selectedSheets)
sourceWB.Sheets(selectedSheets(i)).Copy Before:=tempWB.Sheets(1)
Next i

' Open the target workbook
Set targetWB = ThisWorkbook

' Copy the sheets from the temporary workbook to the target workbook
Application.ScreenUpdating = False

For Each sheet In tempWB.Sheets
sheet.Copy After:=targetWB.Sheets(targetWB.Sheets.Count)
Next sheet

Application.ScreenUpdating = True

' Close the temporary workbook without saving changes
tempWB.Close SaveChanges:=False

' Close the source workbook
sourceWB.Close SaveChanges:=False

MsgBox "Selected sheets transferred successfully.", vbInformation
End Sub

 

[frabulator]

Hello and welcome to the forum!

It makes it a lot easier to read code when you put it inside the code bracket. You can do that by clicking the "</>" icon above the reply/post body of text when you are typing.

That being said, it looks like there is a lot of things that could go wrong with your code. First, you need to remove the

On Error Resume Next

This code will skip over any code that is not right. When you are learning you do not ever want to use this. If you do use it, you also want to end the 'skip any error' by including the line:

On Error goto 0

With that code removed, run your project again and see where it locksup at. From there you can research your individual problems and address them one at a time.

------------------

Side notes:
* Using the 'Record Macro' function is really great when you are first starting off.
* You can also enter breakpoints into your VBA code so you can step through your code one line at a time. This is wonderful for decoding projects and seeing what variables are being used and where they are being referenced to.