CODEWITHSUNDEEP
vbaexcelcopy
Chris
Chris

Reputation: 193

"Subscript out of Range" when attempting to copy worksheet

Private Sub Add_Click()
ActiveWorkbook.Worksheets(ComboBox1.Value).Copy Before:=Workbooks(link.Value).Worksheets("Contract")
End Sub

This is the I am 100% sure that link and ComboBox1 have the values that I'm looking for.

Can someone tell me why I keep getting the error "Subscript out of Range"?

Upvotes: 0

Views: 4048

Answers (1)

A.S.H
A.S.H

Reputation: 29332

The problem comes from:

Before := Workbooks(link.Value)

From your comments it appears that link.value holds the full path name of the file. The method Workbooks(somename) needs the somename to be a name, such as Workbooks(test.xls), without the path.

Try to get the file name without a path from the browse dialog and feed it to this function without the path. Eventually, to fix it fast, you can use this:

ActiveWorkbook.Worksheets(ComboBox1.Value).Copy Before:=Workbooks(Right(link.Value, Len(link.Value) - InStrRev(link.Value, "\"))).Worksheets("Contract")

The added code extracts the file name from the full path given in link.value.

Upvotes: 1

Related Questions