Looping Through Worksheets

[DLStryker]

I just spent 4 hours copy and pasting data from multiple worksheets in a workbook to a blank worksheet. Can someone help me with the code to loop through all of the worksheets in a workbook and in doing so, copy certain cell contents into my blank worksheet? I cannot write VBA code. I can take previously written code and adjust it for my specific needs though. Would anyone be willing to help?? I can provide you more specifics. I have researched “Looping through Worksheets”, but just cannot get it.

Thanks
Dave

[Paul_Hossler]

This might get you started

Option Explicit

Sub CopyData()
    Dim ws As Worksheet
    
    
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name = "Destination" Then GoTo GetNextSheet
            If .Visible <> xlSheetVisible Then GoTo GetNextSheet
            If .ProtectContents Then GoTo GetNextSheet

            .Range("A1:Z26").Copy Worksheets("Destination").Cells(1, 1)
        End With

GetNextSheet:
    Next
End Sub

[jolivanes]

You might want to change

.Range("A1:Z26").Copy Worksheets("Destination").Cells(1, 1)

to something like

.Range("A1:Z26").Copy Worksheets("Destination").Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1).Offset(1)

[DLStryker]

Paul_Hossler - jolivanes, thank you so much for your help. What I have is a workbook with many worksheets. The data is in the same location on each worksheet. Just different dollars amount and each worksheet represents a different cost center. What I would like to have is for a macro to go to the first worksheet, select specific cells of data and copy that data into a blank worksheet. For example, I would like the data block created to look like.

Cost Center	Revenue	Expense	Net Income	<<This is just some column headings
AB4568	150	120	30	<<This is four fields of data from the first worksheet
AC4568	140	120	20	<<This is four fields of data from the second worksheet

Can you attach Excel spreadsheets to posts in this forum?

Regards,
Dave

[DLStryker]

You might want to change

.Range("A1:Z26").Copy Worksheets("Destination").Cells(1, 1)

to something like

.Range("A1:Z26").Copy Worksheets("Destination").Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1).Offset(1)

Very helpful! The data was being written on top of itself without that change in code.

[Paul_Hossler]

I can take previously written code and adjust it for my specific needs though

Since you didn't say where you wanted to copy the data, AND that you could adjust it for your specific needs, I left that part as a homework assignment

[jolivanes]

Is all that information, Cost Center, Revenue, Expense, Net Income, in consecutive cells? What is the range?
If not, what are the cell addresses you want to copy across?
Are the sheet names the cost center names by any chance?
Before pasting into the "collector" sheet, does it need to be cleared?

To attach a workbook, click on "Go Advanced" at the bottom right hand site and follow info.
At the top is a FAQ site also.

[Paul_Hossler]

Can you attach Excel spreadsheets to posts in this forum?

Yep, at the bottom right of the post box (i.e. where you type -- don't know what it's really called) there's [Go Advanced] which give you more options

Use the paperclip icon

It helps if your sample WB has the 'as is' and the 'to be' included, along with any business rules and restrictions


Capture.JPG

[DLStryker]

OK. I think I have the sample file attached. I think it will be self explanatory when you view the file.

Looking to take data from specific cells on a tab, input that data into the "Destination" tab and then do that some process again for all tabs in the workbook.

Thanks!!!

[DLStryker]

Attached is a file with code that does something similar. This code opens a workbook on a shared drive, copies the data from certain cells into a blank worksheet, then it does the same thing for the next workbook in the shared drive and copies that data one line below the previous.

I have been able to adjust this code to refer to different workbooks in different paths in the shared drive and to also collect different data from each worksheet.

BUT, that's about the extent of my VBA knowledge!!

Thanks again!!!

[jolivanes]

Is this a beginning for you?

[DLStryker]

Is this a beginning for you?

A beginning to use VBA?? Not sure what you are asking.

[DLStryker]

Is this a beginning for you?

AWESOME!!!! jolivanes!! This will save me hours of copying and pasting!! And I mean HOURS! I am working for a small company that has no financial database. I am basically building financial reports off of financial reports. very inefficient, but no other option.

Now I am going to go play with your code to understand what does what. Then I'll make adjustments to have the code pull additional data fields.

Thank you very much!!

[DLStryker]

OK. I added additional worksheets to the workbook. The code collects the data as expected, but stops after the 8th worksheet. Then I get a VBA error pop up.
Error.png

I have attached the file with the additional worksheets that is getting the error code.

Many thanks!

[jolivanes]

There are lots of different ways of doing it. I just used formulae here like you would if you did it manually.
I'll see later to do it a different way. I am curious about how fast/slow it is with the amount of sheets you mentioned.
You also need to format the results. That would be easiest when all the copying/pasting is finished.
If you need anymore help, come back here and ask.

[jolivanes]

Re: Your post #14
If you take the apostrophe out of the Sheet Name, it'll work. That is the disadvantage of formulae.
Do you want to check all the sheet names if they have characters that are not allowed in the name, and if they do, change it?

[DLStryker]

My workbook had a total of 30 sheets. The macro would pull the data for the first 8 sheets and then stop and give me the error code. It would not continue on through the next 22 sheets. The macro pulled the data for the first 8 sheets in half a second.

[jolivanes]

Did you remove the apostrophe in sheet #9?
The sheet name is
Glance - Summa (092 - aloft O')
change it to
Glance - Summa (092 - aloft O)

[DLStryker]

Did you remove the apostrophe in sheet #9?
The sheet name is
Glance - Summa (092 - aloft O')
change it to
Glance - Summa (092 - aloft O)

Yes sir. I removed the apostrophe. All is good!!! So I pulled the data for the whole company..... 175 tabs in the worksheet. Your macro pulls the data from all 175 tabs in a split second!! 6 hours of copying and pasting previously. I'm about to wet myself I am so freakin relieved!! LOL!

[DLStryker]

Found a glitch in my methodology of pulling the data. Each and every tab is the same column wise, but not row wise. The data I am wanting to extract is in the same column on each tab, but the rows can vary. This is a system generated report I am working from and it will add additional rows, or exclude rows, depending on if data exists for that specific row. The rows that can be added or excluded is not rows that I am attempting to reference in the macro. They are detail line items and I am only interested in the sub-total lines.

So... I cannot use exact cell references in the macro. The column reference will be static, but not the row reference. I need something similar to a vlookup formula in the macro.

For example, I would be looking for "Total Operating Revenue" in the data and then go over x number of cells and retrieve that data.

Have any suggestions for that issue??

Kind regards,
Dave