Issue with variable (run-time error '9' subscript out of range)

[Mitch149]

Good day!

I have a little issue when copying sheets to another file. I start to copy a first sheet from the file X to a new file Y (see below "ChDir ...", this is working properly. Then I change some selections in the file X and copy the same sheet to the file Y, and so on (see below "Sheets("REP CTY-BRD-MTH").Copy After..."). As I would like the copies being performed one sheet after the other, I am using the variable k which is incremented at the end of the code line ("Sheets(k)). But VBA always stops when trying to copy the second sheet in the new file Y, highlighting the line in yellow with the message "run-time error...". If I put "1" instead of "k", the copies will always be in the same position and not in the following order, but at least it is working.

Any idea why this variable is not accepted ?

Thanks a lot in advance and best regards

-----------------------------------------------------------------------------
Dim k As Integer
...
k = k + 1

ChDir "R:\SGCS\KPI\" & Année & "\Management reports"
ActiveWorkbook.SaveAs Filename:= _
"R:\SGCS\KPI\" & Année & "\Management reports\" & Année & "-" & Mois & "\Brands\KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
...
Sheets("REP CTY-BRD-MTH").Copy After:=Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx").Sheets(k)
-----------------------------------------------------------------------------

 

[RoryA]

Welcome to the forum.

That error means that your k value at the time of the error is greater than the number of sheets in the workbook. We can't see the workbook, and you seem to have left out a chunk of code, so it's pretty hard to say why that is, other than the fact that on first run of that last line, k will be 2 - are there 2 sheets in the workbook to start with?

 

[bobsan42]

This happens when you ask for something that is not there.
e.g. you have 4 sheets in a workbook but you ask for sheet #5.

 

[Mitch149]

Welcome to the forum.

That error means that your k value at the time of the error is greater than the number of sheets in the workbook. We can't see the workbook, and you seem to have left out a chunk of code, so it's pretty hard to say why that is, other than the fact that on first run of that last line, k will be 2 - are there 2 sheets in the workbook to start with?

Hi RoryA and bobsan42 !

Thanks a lot for your messages and first analysis of my request, appreciated!

Let me give some additional information related to the workbook and results:

Thanks again and receive my best regards
Mitch

 

[Mitch149]

This happens when you ask for something that is not there.
e.g. you have 4 sheets in a workbook but you ask for sheet #5.

Hi bobsan42,
I have replied to RoryA and to you in the same message, thanks to refer to it.
Mitch

 

[bobsan42]

to avoid such errors you may use Workbook.Sheets.Count:

Sheets("REP CTY-BRD-MTH").Copy After:=Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx").Sheets(Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx").Sheets.Count)

This will always give you the last sheet.

 

[bobsan42]

A word of advice - in general, but especially when working with multiple workbooks:
(Maybe your code is working correctly, but you only shared a piece of it so I cannot tell)
- create workbook objects to use instead of using long and complicated names in each statement

set wbSource = ThisWorkbook
set wbTarget = Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx")

This should be your general approach also for Sheets, Ranges, etc.
- ALWAYS specify explicitly the workbook which contains the sheets you operate with.

wbSource.Sheets("REP CTY-BRD-MTH").Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)

- Do not rely on ActiveWorkbook. Avoid Activating and Selecting object unless absolutely necessary (very rare)
- Avoid unnecessary operations in general: do you really need ChDir?
On most occasions ChDir needs to be combined with ChDrive to have any effect. Can check it with CurDir.
- JFI - there is a small difference between the Sheets and Worksheets collections

 

[Mitch149]

to avoid such errors you may use Workbook.Sheets.Count:

Sheets("REP CTY-BRD-MTH").Copy After:=Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx").Sheets(Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx").Sheets.Count)

This will always give you the last sheet.

Thank you very much, it is working well !!

 

[Mitch149]

A word of advice - in general, but especially when working with multiple workbooks:
(Maybe your code is working correctly, but you only shared a piece of it so I cannot tell)
- create workbook objects to use instead of using long and complicated names in each statement

set wbSource = ThisWorkbook
set wbTarget = Workbooks("KPI_MGT_BRD_" & Right(Année, 2) & "-" & Right(Mois, 2) & "_TCM-NDC-DET.xlsx")

This should be your general approach also for Sheets, Ranges, etc.
- ALWAYS specify explicitly the workbook which contains the sheets you operate with.

wbSource.Sheets("REP CTY-BRD-MTH").Copy After:=wbTarget.Sheets(wbTarget.Sheets.Count)

- Do not rely on ActiveWorkbook. Avoid Activating and Selecting object unless absolutely necessary (very rare)
- Avoid unnecessary operations in general: do you really need ChDir?
On most occasions ChDir needs to be combined with ChDrive to have any effect. Can check it with CurDir.
- JFI - there is a small difference between the Sheets and Worksheets collections

Thank you very much also for your precious advices, appreciated that you have to have invested that time ! I have these long names only in 3 lines but I will adapt them for a more professional and readable approach.
With my best regards,
Mitch

 

[Joe4]

Mitch149,

Please Note: In the future, when marking a post as the solution, please mark the post that contains the solution (not your own post acknowledging that some other post was the solution).
When a post is marked as the solution, it is then shown right underneath the original question so people viewing the question can easily see the question and solution in a single quick glance without having to hunt through all the posts.

I have updated this thread for you.