CODEWITHSUNDEEP
vbaexcel
user2437957
user2437957

Reputation: 9

VBA can't recognize my already open workbook

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

Upvotes: 0

Views: 3976

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

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.

Upvotes: 0

Karthick Anbazhagan
Karthick Anbazhagan

Reputation: 353

Set wkb2 = Workbooks("A.xlms")

Replace this line to

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

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

Upvotes: 2

Related Questions