Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Run-Time Error 9 - Trying to refer to cells in worksheet

  Asked By: Diem    Date: Sep 12    Category: MS Office    Views: 899
  

I have a whole stack of files that I need to copy into a report.
There are two for each Account number. The account numbers are
listed in a worksheet. The files are saved as the account number
then _06 and _07 on the end. I am trying to write a loop to cycle
through the account numbers and copy the files in. But I am getting
a "Run-time Error 9 - Subscript out of range" error.

I'm only new to doing this sort of code and I have no idea what I'm
doing wrong. Would someone let me know?

Sub Test()

i = 3
Sheets("Final Report").Select

Do While Cells(i, 1) <> ""


Workbooks.Open Filename:=Cells(i, 1) & "_06.xls"
Sheets(Cells(i, 1) & "_06.xls").Copy Before:=Workbooks("CHEP
Milk Run Transport " & Format(Now, "yyyy-mm") & ".xls").Sheets(1)
Windows(Cells(i, 1) & "_06.xls").Close

Workbooks.Open Filename:=DocName & "_07.xls"
Sheets(DocName & "_07").Copy Before:=Workbooks("CHEP Milk
Run Transport " & Format(Now, "yyyy-mm") & ".xls").Sheets(1)
Windows(DocName & "_07.xls").Close

i = i + 1
Loop


End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Gloria Cook     Answered On: Sep 12

Please correct the code  as follows. I will not write  the full thing because
the error  is only in the beginning few lines.

Dim FileString as string
i=3
Do while cells(i,1) <> ""
Sheets("Final....").select
FileString = Cells(i,1) & "_06.xls"
Workbooks.Open Filename:=FileString
Workbooks(FileString).Sheets(1).Copy Before:=Workbooks("CHEP
......xls").Sheets(1)
Windows(FileString).Close

 
Answer #2    Answered By: Kaysah Mohammad     Answered On: Sep 12

thanks for going to so much trouble, it works perfectly. I'm
so glad I stopped and asked for help. I can even see the how and why.

Awesome.

 
Answer #3    Answered By: Gus Jones     Answered On: Sep 12

Which line is it crashing on, and what exactly is in the subscript?

 
Answer #4    Answered By: Hisa Yoshida     Answered On: Sep 12

Try using

Workbooks.Open Filename:=Cells(i, 1).value & "_06.xls"

 
Didn't find what you were looking for? Find more on Run-Time Error 9 - Trying to refer to cells in worksheet Or get search suggestion and latest updates.




Tagged:            

 
 
 
 

Related Post