Step 1: Can you Loop Through Multiple Excel worksheets in one workbook (Copy and paste all to new worksheet or Workbook)

[LearnVBA83]

Hi all you VBA Super Heros!

I finally got back into a role where i use Excel heavily and boy have i lost a lot of skills. I'm trying to automate a couple items and have a couple questions that I will post threads about!

Step 1: If i have a workbook with a bunch of worksheets that can change each month like the one in the attached image... Is it possible for VBA to loop through each worksheet and copy and paste the data into a new worksheet (so that all data is in one worksheet)? Here's the hard part! I only want the Loop to go through the worksheets with the 6 digit numbers in the name (so it would even pick up the one called 296135-GSO Ramp b/c the 6 digit number is in the name). I also want it to copy the name of the worksheet (only the 6 digit numbers) and paste it in each row in column A where there is something in column B of the file...

Image A: Shows what it would look like before the Macro
Image B: Shows what it would look like after the macro added the worksheet name

Then it would paste the data in a new worksheet, loop to the next tab that has the 6 numbers in the name and add the 6 digit number in column A where there is data in column C, copy paste to the new worksheet, and loop loop loop until down.

At the end i would have a new workbook or new worksheet that had all those tabs pasted into one worksheet and the tab name (6 digit number) in column A so you can see the change in worksheets

 

[Micron]

If I understand the problem, then should be fairly easy to loop over worksheets and get only the names that start with 6 digits - as long as they're before any text and not after: IsNumeric(Left(000123,6)) returns True. If not at the beginning, that adds a level of complexity. Not sure if it's wise to duplicate so much data in the same workbook as it will eventually get quite large?

 

[LearnVBA83]

If I understand the problem, then should be fairly easy to loop over worksheets and get only the names that start with 6 digits - as long as they're before any text and not after: IsNumeric(Left(000123,6)) returns True. If not at the beginning, that adds a level of complexity. Not sure if it's wise to duplicate so much data in the same workbook as it will eventually get quite large?

Once i figure the first part out which is how to loop through the worksheet with numbers and how to paste the worksheet name down column A where there is data in column B then i will do some other macros in the loop to basically filter and only copy 4-5 lines per tab. I was just trying to trouble shoot the big problem first. I understand the IsNumeric formula you wrote but how do you write VBA code to loop through only those numeric tabs?

 

[Micron]

Something like

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    If IsNumeric(Left(ws.Name,6)) Then
        'do copy/paste stuff
    End If
Next

If you're going to tackle this in steps you can Google those steps. I'll bet if you searched on "Excel vba loop over sheets in a workbook" you'd find lots of similar examples.

 

[LearnVBA83]

Something like

Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
    If IsNumeric(Left(ws.Name,6)) Then
        'do copy/paste stuff
    End If
Next

If you're going to tackle this in steps you can Google those steps. I'll bet if you searched on "Excel vba loop over sheets in a workbook" you'd find lots of similar examples.

This looks promising. Do you happen to know if VBA can copy the worksheet name (tab name) into that worksheet? If you Look at my example A that is the worksheet for the tab labeled 008169. I would like for the macro to copy that worksheet name of 008169 and paste it in that same worksheet so it looks like example B. Basically to paste the worksheet name in column A anywhere there is data in column B. Is that possible?

 

[Micron]

Do you happen to know if VBA can copy the worksheet name (tab name) into that worksheet?

Basically to paste the worksheet name in column A anywhere there is data in column B. Is that possible?

For sure on both counts. The part I'm not following is the before and after you showed. You mention posting data in a new sheet, but what you show looks like the same sheet but with some sheet name pasted into column A where there is data in column B. Not clear to me if image B is A with already copied data from somewhere but with the sheet name pasted, or if it's something else. I can be thick at times I guess.

 

[LearnVBA83]

Hi Micron!

Thank you for all the help. I may create a separate post for everyone to see. I created a SnagIT video and loaded it to youtube to show exactly what i'm trying to do. I think it would be easy for a VBA wizard to do this, but i'm just learning. See below if you'd like to take a look.

Thank you!

 

[Micron]

You sure have expounded upon the ask! I got as far as 7 minutes but I knew before that point that this isn't something I'd like to take on. I'm not as proficient with Excel vba as I am with Access, so most of that I'd have to Google to get the correct syntax for what I know I'd need to do. I'd say for me that is about a 2 to 3 day job, allowing for the parts I didn't view. Maybe you'll get a solution but I'd be surprised if anyone will do all that for free. Good luck!

 

[LearnVBA83]

You sure have expounded upon the ask! I got as far as 7 minutes but I knew before that point that this isn't something I'd like to take on. I'm not as proficient with Excel vba as I am with Access, so most of that I'd have to Google to get the correct syntax for what I know I'd need to do. I'd say for me that is about a 2 to 3 day job, allowing for the parts I didn't view. Maybe you'll get a solution but I'd be surprised if anyone will do all that for free. Good luck!

I actually just submitted it to ExcelRescue for a quote. I would think a VBA expert could knock that out rather quickly. Thank you for your help. I honestly think the first part that you may have solved is the hardest.

 

[Micron]

I honestly think the first part that you may have solved is the hardest.

Definitely the easiest.