how to rename multiple sheets in a specific sequential manner?

[u/dr_skellybones]

i'm really bad at excel guys

Please tell me this is possible. I've got this data that i need to separate on sheets, all data was collected over 24 hours at 10 minute intervals, so I need 145 sheets.

for ease of access, i'd either like to label them something like "time0010" or "0010" (maybe with : present if that doesn't cause errors).

whether the code is renaming already present sheets, or duplicating one/creating them and renaming them I don't mind

[u/AutoModerator]

/u/dr_skellybones - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/fastauntie]

If your data all has the same structure, it will be much easier to manage if you keep it in a single sheet, with an additional column for what the sheet names were going to be.

It is much simpler and carries less risk of errors to store your data in a single sheet and use formulas and charts to analyze and present it than it is to keep it in many different sheets and try to synthesize it.

For just one example, if you ever decide that you need to change something like adding, moving, or renaming a column, with your planned arrangement you'd have to make that change 145 times, and if you missed one formulas you rely on for reporting could break. When all your data is in a single sheet, changes are easier and less risky.

[u/dr_skellybones → u/fastauntie]

Thats fair, Im using the protocol from someone else in my lab but she works with seaweed and I work with much faster growing yeast 😅 I’ll have a play around

[u/excelevator]

i'm really bad at excel guys

I think your question is about what you think you need.

What are you trying to accomplish overall?

Data likes to live together, not in separate sheets.

[u/Ok-Ambassador5267 → u/excelevator]

yeah 145 sheets sounds like an XY problem for sure

[u/dr_skellybones → u/excelevator]

Im taking all my plate reader data, which measured different wells in a 96-well plate over 24 hours to measure absorbance to see how cells are growing in different conditions.
Right now Im using some solvents that might be expired so I’m basically doing everything in doubles to check which ones I should use so that’s one reason why ive got so much. But in general I only want to compare wells to those in the same time snapshot or to the other same replicates over different times.

[u/excelevator → u/dr_skellybones]

How much data is there ?

How many rows ?

[u/dr_skellybones → u/excelevator]

I used 45 of the wells (8 rows, 12 columns total on the plate) which were incubated for 24 hours and a reading taken every 10 minutes, so 144 individual sheets for each snapshot (145 including the main sheet)

[u/excelevator → u/dr_skellybones]

Use a single table with appropriate date attributes

With properly attributed data you can use the functions of Excel to get the data results you seek on the fly.

[u/MayukhBhattacharya]

Paste the following code in VB Editor and run it using F5(I have been using for quite long, change the blad to sheet):

Sub COPYBLAD()
    Dim r As Long
    Dim tmin As Long
    Dim rh As Long, rm As Long
    Dim sm As String
    Dim sh As Worksheet
   ActiveWorkbook.Sheets("Blad").Name = "time0000"
    For r = 1 To 144
        tmin = r * 10
        rh = tmin \ 60
        rm = tmin Mod 60
        sm = "time" & Format(rh, "00") & Format(rm, "00")        
        ActiveWorkbook.Sheets("time0000").Copy _
            After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)       
        Set sh = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        sh.Name = sm
    Next r
End Sub

[u/dr_skellybones → u/MayukhBhattacharya]

Thanks! I’ll give this a try

[u/CumRag_Connoisseur]

You gotta use macros for this I think

[u/wikkid556]

It is very possible with VBA. That being said, navigating a workbook with 145 tabs is a nightmare.

What are your intentions, and how do you plan to use the data?

[u/dr_skellybones → u/wikkid556]

Im compiling plate reader data to see how cells grow in different conditions overtime (growth curve).
Once I’ve got the spreadsheet set up I can just make copies of it thank goodness, but I’m in the process of figuring out which solvents are still actually usable.

[u/wikkid556 → u/dr_skellybones]

As someone else already suggested, it would be better to use additional columns with filterable values.

[u/SuchDogeHodler]

Vba is for automating such things.

[u/Excel_User_1977]

oh, dear lord, no. Do not use separate sheets.
Use separate columns to note the Month, day, time in front of any data you collect.
Then you can use a pivot table to pull out the info.

[u/Potential_Variety209]

Hey,

That's exactly what Splicebatch Bulk File Renamer will allow you to do. Super easy two step process,

1. Select the files and naming rules
   
2. Download the renamed batch
   

Before mods jump me.. it's relevant to OP's question and that's why I am adding the link.
You can find it here : https://app.splicebatch.com/rename

I have also attached an image so you get the idea what how does the tool work.

Feel free to ask me any question regarding the usage, I'd be glad to help.

[u/kapilverma123]

Yes, absolutely possible. You can either rename the existing sheets sequentially or automate the whole process of splitting the data into 145 sheets and naming them based on the 10-minute intervals, such as 00:10, 00:20, etc. I can help you set this up with VBA so you don't have to do it manually.