Macro to change multiple files Sheet name

[Raychin]

Hi there! I need a macro to change the name of the sheet of multiple .xls files in a folder. The sheets in the files are named like date and i need them be changed to Sheet (1) so after that standartisation i can run a macro to copy the numbers from B2:B25 to a specific Master file column. I tried several VBA codes but non of them working properly. The files sheet names is like this : IDM Result for 2021-01-03 to be change to Sheet1. I attached two images of what is the name of the sheet from files i need to be changed and place (column) where should be B2:B25 data should go afterwards.
Thank you in advance!

 

[Joe4]

Welcome to the Board!

How exactly are you copying this data? Are you intending to use VBA for it?
If so, you may not need to rename any sheets, because regardless of the sheets name, you can refer to it by index number in your VBA code.
The format is:
Sheets(index number)

Here is a simple example to show you how you can use that. Let's say that you wanted to return the name of the 3rd sheet in your workbook to the screen. You could use:

MsgBox Sheets(3).Name

So, you could loop through all your sheets with a loop somethning like this:

    Dim i As Long
    
    For i = 1 To Sheets.Count
        MsgBox Sheets(i).Name
    Next i

 

[Raychin]

I need to copy the B column data from the second image, to the D column of the Master file. The files are daily values and i need to copy them in one single column (D) for further data management. So i have roughly 500 files to open and copy/paste the B column from them if do it manually. That's why i need a VBA code to do it, assuming the sheets name is the same in all data files it will be easier to create a copy/paste VBA.

 

[Joe4]

I need to copy the B column data from the second image, to the D column of the Master file. The files are daily values and i need to copy them in one single column (D) for further data management. So i have roughly 500 files to open and copy/paste the B column from them if do it manually. That's why i need a VBA code to do it, assuming the sheets name is the same in all data files it will be easier to create a copy/paste VBA.

OK, do you understand what I am saying in my previous post, that if you are using VBA, you DON'T actually need to know the exact sheet name in order to copy from it in VBA?
You can refer to it by the sheet index number rather than the sheet name.

Also, these 500 files, how many sheets are in each of them?
Are they all single sheet workbooks, or do the contain multiple sheets?
If they contain multiple sheets, which one do you want to copy from and which ones do you want to skip?

For example, let's say each workbook has 3 sheets. The first is a summary sheet, that you don't want to copy from, and the last two contain the data you want to copy.
That is the sort of description I am asking for with that last question.

 

[Raychin]

Yes i do Just clarified the issue.
The daily files are single sheet workbooks. They were gathered to dir Daily files, from where should be opened and extract the data from the column B and copied to column D of Master file Sheet 1.

 

[Joe4]

OK, if they only have one sheet, then you really don't need to know the sheet name, as when you open the file, you will always be on the sheet you need.
You could preface all your ranges with "ActiveSheet" if you really wanted to, but that isn't even really necessary, as there are no other sheet options in a one sheet workbook!
So I don't think there is really anything special you need to do with sheet names.

 

[Raychin]

Aha ok, thank you!

 

[Joe4]

You are welcome.
If you run into any issues, post back, and show us the part of the code you are having trouble with.

 

[Raychin]

OK, i have to figured out the code to consolidate the multiple file data into one column of the master file.

 

[Joe4]

Note that you can dynamically find the last populated row in column D at any time, with a line of code like this:

Dim lastRow as Long
lastRow = Cells(Rows.Count, "D").End(xUp).Row

If you want to go to the first blank row AFTER the last populated row, you can use the same logic, and add an Offset command to move down one row, i.e.

Cells(Rows.Count, "D").End(xlUp).Offset(1, 0).Select

So you probably want this dynamic calculation within your loop to find the next available row to paste to each time.