Workbook names stored as variables cause "Subscript out of range" error when copying/pasting.

[Kandel]

I'm trying to use this macro to copy data from one unknown workbook and paste it into another (also unknown). It should pop up two userforms, the first asking which file to use as the source and the second asking which file to use as the paste target. I trimmed off the .xls file type (although I have tried it with and without this part) by just removing the last 4 characters. This all works correctly and the MsgBox lines pop up with the correct names of the files.

When the code gets to the command to copy and paste from workbook to workbook I get a run-time error 9: subscript out of range. I believe that means that the code cannot find the workbooks and the name is wrong. If I type in the name of the workbooks (exactly as seen in the msgbox pop ups) then the copy/paste runs fine, but with the variable in the Workbooks("variable") portion it continues to give me that error.

Does anyone have any idea why this would be happening or what a solution might be?

Thanks for your help.

---

Module 1

Option Explicit

Public Source As String
Public TargetFile As String
Public Stopped As Boolean


Sub test()

Stopped = False

UserForm1.Show

If Stopped Then Exit Sub

UserForm2.Show

If Stopped Then Exit Sub

Source = Left(Source, Len(Source) - 4)
TargetFile = Left(TargetFile, Len(TargetFile) - 4)

MsgBox Source
MsgBox TargetFile

Application.DisplayAlerts = False

Workbooks("Source").Sheets("Historical Analysis").Range("B4:C4").Copy Destination:=Workbooks(TargetFile).Sheets("Historical Analysis").Range("B4:C4")

Application.DisplayAlerts = True

End Sub

---

UserForm1


Option Explicit

Private Sub CommandButton1_Click()
Source = Me.ComboBox1.Value
Unload Me
End Sub

Private Sub CommandButton2_Click()
Stopped = True
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkb As Workbook
Me.Label1.Caption = "Please select the source file."
With Me.ComboBox1
For Each wkb In Application.Workbooks
.AddItem wkb.Name
Next wkb
End With
End Sub

---

UserForm2


Option Explicit

Private Sub CommandButton1_Click()
TargetFile = Me.ComboBox1.Value
Unload Me
End Sub

Private Sub CommandButton2_Click()
Stopped = True
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkb As Workbook
Me.Label1.Caption = "Please select one of the following files..."
With Me.ComboBox1
For Each wkb In Application.Workbooks
.AddItem wkb.Name
Next wkb
End With
End Sub

---

 

[Anthony47]

Workbooks("Source").Sheets("Historical Analysis").Range("B4:C4").Copy Destination:=Workbooks(TargetFile).Sheets("Historical Analysis").Range("B4:C4")

Do you have a workbook named Source or should it be

Workbooks(Source).Sheets("Historical Analysis").Range("B4:C4").Copy Destination:=Workbooks(TargetFile).Sheets("Historical Analysis").Range("B4:C4")

??

Bye