CODEWITHSUNDEEP
vbaexcelcomboboxworksheet
Les Programmer
Les Programmer

Reputation: 121

Accessing multiple worksheets using excel 2013 vba

I have 3 worksheets that I have data in. The main worksheet has a sheet named Lists that contains various lists for combo-boxes. I am trying to make the lists dynamic i.e. have the ability to add entries and have them show the next time the user goes to the combo-box on the user-form.

I have this code, which works fine ONLY if I'm in the Lists sheet of the correct workbook:

 ThisWorkbook.sheets("Lists").Range("I1", Range("I" & Rows.Count).End(xlUp)).Name = "DriverList"

However, if I'm in another worksheet or another workbook (all in the same directory) I get a

runtime error #1004.

I have tried both of the following lines with no success:

dim spath, sfile as string
dim wbCharityBins as Workbook
spath = "Z:\Charity Bins 2015 Test Data\"
sfile = "Charity Bins 2015 - test.xlsm"
Set wbCharityBins = Workbooks.(spath & sfile)
wbCharityBins.sheets("lists")... etc

or 

Workbooks(spath & "Charity Bins 2015 - test.xlsm").sheets("Lists").Select

These two tries give:

Subscript out of range, error 9

I have an initialization routine that opens all the workbooks before I get to the above problem, so the workbooks are open.

Any suggestions on how to access the worksheet from another sheet or workbook would be appreciated.

Upvotes: 1

Views: 153

Answers (2)

Les Programmer
Les Programmer

Reputation: 121

Initial testing looks good. Has the problem arisen because its a dynamic combo list? When just passing data from a userform textbox should I be able to just use:

workbookx.sheets("y").range("a1") = userformTextBoxContents.

Remember all workbooks are open.

Upvotes: 0

ryankey721
ryankey721

Reputation: 53

The problem is Range("I" & Rows.Count).End(xlUp) which is referencing the range of the ActiveSheet, instead of "Lists".

Possible solution:

  With ThisWorkbook.Sheets("Lists")
    .Range("I1", .Range("I" & Rows.Count).End(xlUp)).Name = "DriverList"
  End With

Upvotes: 2

Related Questions