Help with incrementing a filename/text by 1

marcusj07

New Member
Joined
Jul 27, 2011
Messages
2
I am attempting to open about 650 excel files in a folder and I want to use a for loop to do it. I have tried just throwing a for loop at the begging of my code that increments to 650 and the rest of my workbook manipulation after that. However whenever I go to open a workbook with the variable I am incrementing my for loop with in the name as opposed to 1, 2, 3, ...650 it says that I it can't find file y. I know that I can open everyfile in a folder and do the same manipulation to each workbook but the problem is that evey sheet in each workbook is also incremented by 1. If I could find a way of incrementing this text variable by one when it is in the "" ( for y = 1 to 650 Sheets("sheety") instead of having to call each sheet individually each time like workbook.("workbook1").Sheets("sheet1") workbook.("workbook1").Sheets("sheet2")...) I could use it for both applications and be done. Thanks.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mvptomlinson

Well-known Member
Joined
Mar 10, 2008
Messages
2,638
In your loop, could you use:

Workbooks("workbook"&y).Sheets("Sheet"&y)....?

Tough to understand your paragraph of text, but it sounds like you have Workbook1 and Sheet1, Workbook2 and Sheet2, etc.? If that's not the case, try to explain more concisely, e.g.

"I want to loop through 650 sheets, named Workbook1 through Workbook650. In each workbook is one sheet, and its name corresponds to the Workbook name. For example, Workbook1 has Sheet1 and Workbook2 has Sheet2."
 

Forum statistics

Threads
1,144,376
Messages
5,724,001
Members
422,530
Latest member
Badpoisondwarf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top