VBA can't recognize my already open workbook

0
votes

It says Subscript is out of range. The workbook is already open. I have tried with a path ex:Set wkb2 = Workbooks("d:/A.xlms"). Also I have tried this Set wkb2 = Workbooks.open("d:/A.xlms") with workbook not open. It all returns error mentioning that the file doesn't exist.

Sub CopySourceToTarget()
Dim wkb1 As Workbook
Dim sht1 As Worksheet
Dim wkb2 As Workbook
Dim sht2 As Worksheet

Application.ScreenUpdating = False

Workbooks("A.xlsm").Activate

Set wkb1 = ThisWorkbook
Set wkb2 = Workbooks("A.xlsm") ----THIS LINE RETURNS THE ERROR----

Set sht1 = wkb1.Sheets("Product codes")

Set sht2 = wkb2.Sheets("Product")

sht1.Range("A8:AZ65000").Copy

sht2.Range("A4").PasteSpecial xlPasteValues

Application.CutCopyMode = False

wkb2.Close True

Application.ScreenUpdating = True

End Sub

I also tried this code and it's not working

Sub CopySourceToTarget()
Dim Source As Range, Target As Range

Set Source = Workbooks("Local Codes Creation1.xlsm").Worksheets("Product Codes").Range("A8:AZ6500")
Set Target = Workbooks("A.xlsm").Worksheets("Products").Range("A4:AZ7500")

Source.Copy Destination:=Target

End Sub
2
vbaexcel
xlms-> xlsm? – Bathsheba
You should not be addressing a workbook by name. Use ActiveWorkbook instead. – braX
Thats actually really bad advice. Referring to a workbook by name is probably a better practice then referring to activeworkbook. It does depend on the situation but if we're going to speak "in generally" i would think explicitly stating it is better. – Doug Coats
I've just noticed this: "It all returns error mentioning that the file doesn't exist." - are you 100% absolutely sure you're entering the filename correctly - including the suffix? – CLR
Add the following Sub in a module: Sub listallbooks(): For Each wkb In Application.Workbooks: Debug.Print Chr$(34) & wkb.Name & Chr$(34): Next: Exit Sub and then in the immediate window enter listallbooks and hit enter. You should get a list of all workbooks that module can 'see'. – CLR

2 Answers

2
votes
Set wkb2 = Workbooks("A.xlms")

Replace this line to

Set wkb2 = Workbooks.Open("Your Path") for example

Set wkb2 = Workbooks.Open("G:\Book1.xlsm")

0
votes

only worbooks.open require full filename , i mean with path.

For workbooks() , you use as argument only the file name, or index. See microsoft help for more info.

And for the case the workbook is not opened already, wich can and will happen, you need an error handling.

Other way, without error handling , loop in each workbook in workbooks , if name is same as the one you need exit loop.