Working with multiple worksheets in a workbook

Hello Buffala77,

There's a few ways this could be done but it would be best if you could upload a sample of your workbook to a file sharing site such as Drop Box or WeTransfer then post the link to your file back here. If your data is sensitive then please use dummy data and please make sure that your sample is an exact replica of your actual workbook. A few rows of data per worksheet will be ample for us to work with.

This will make it simpler for us to help you and by seeing your actual workbook set out will eliminate guesswork.

I've attached a sample workbook here for you to play with and so you can see how this could work. In this sample, all the data from all source sheets is transferred to the Master sheet when the Test Run button is clicked on. This, in effect, refreshes the Master sheet with any new data from any source sheet. There won't be any duplicates as the Master sheet is first cleared prior to refreshing (I'm assuming that you want to keep all data in the source sheets). Values only are transferred to the Master sheet.

The VBA code which allows this to happen is as follows:-

Option Explicit
Sub Test()
        
        Dim ws As Worksheet
        Dim wsM As Worksheet: Set wsM = Sheets("Master")

Application.ScreenUpdating = False
        
        wsM.UsedRange.Offset(1).Clear
        
        For Each ws In Worksheets
                If ws.Name <> "Master" Then
                ws.UsedRange.Offset(1).Copy
                wsM.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                End If
        Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

I hope that this helps.

Cheerio,

vcoolio.

Vcoolio, 

Thank you so much for your quick response! Here is a link to my excel file:

https://wetransfer.com/downloads/d69328894cf66eefc2714026b0dce96e20220810113526/1d6dac

At first glance, it looks like your sample file does exactly what I need it to. I was only in there for a few minutes but I will mess around in there a little more later this morning. If you look at the file that I uploaded and think there is a better way to get the results I am looking for, please let me know. The code you gave me is a much less involved solution that I thought this would  be. Based on the few things I found while searching around on the internet, I was under the impression that each sheet was going to need its own code.

Thank you again for your help with this! 

buffala

Hello Buffala,

I've had a very quick look at your workbook (it's very late here now) but it appears that you have structured all the source worksheets the same, thankfully! So, I've amended the code to allow for the fixed ranges in the source sheets and I think that we're on track with it:-

Option Explicit
Sub Test()
        
        Dim ws As Worksheet
        Dim wsM As Worksheet: Set wsM = Sheets("20XX YEARLY SALES")

Application.ScreenUpdating = False
        
        wsM.Range("A2:Q1202").Clear
        
        For Each ws In Worksheets
                If ws.Name <> "20XX YEARLY SALES" Then
                ws.Range("A2:Q101").Copy
                wsM.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                End If
        Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Test this in a copy of your actual workbook and see how it works for you then let us know what you think. Run the code from your Master sheet to see the result (you can actually run it from any sheet).

I hope that this helps.

Cheerio,

vcoolio.

Right now we are entering data into the current month worksheet and then copying and pasting our entries into the yearly sales worksheet. While this works just fine, I have found that we sometimes forget to copy and paste before we save and close the workbook so your help just made my day!

Just a quick question, I have another workbook that is identical to this one except it has two additional worksheets that hold different information. The data in the two additional sheets does not need to be copied to the master though. If I wanted to use this code in that workbook, would it still work? Or would there need to be some kind of exception to exclude the data on those worksheets from copying over? 

Hello Buffala,

A very simple way of excluding non-required sheets is to add them to the 'exclusion' line of code which, in post #4, is line #12.

Hence, change that line to:-

If ws.Name <> "20XX YEARLY SALES" And ws.Name <> "Whatever" And ws.Name <> "Whateveragain" Then

then add the adjusted code to your other workbook. Just add the correct sheet names and all should be fine in the other workbook but you may want to check the ranges that are to be copied and pasted. These may differ in your other workbook.

If you need any further help with this, just come back when you're ready.

I hope that this helps.

Cheerio,

vcoolio.

That seems simple enough. I'm planning to really get into this more over the weekend but if I get stuck, I will definitely let you know. Again, I appreciate your help with this!

You're welcome Buffala. I'm happy to have been able to assist.

Good luck with your project.

Cheerio,

vcoolio.