Working with multiple worksheets in a workbook

Solved
buffala77 Posts 4 Registration date Tuesday August 9, 2022 Status Member Last seen August 11, 2022 - Aug 9, 2022 at 10:25 AM
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 - Aug 11, 2022 at 07:12 PM

Hello,

I am completely new to all of this so please bear with me. I am new to forums and how they work so I apologize in advance if this is a repeat question. I have searched all over and have not been able to find the proper solution for my issue. It's possible I am just wording or explaining things wrong. Here is my issue:

I have an excel workbook with multiple sheets, there is one sheet that has all orders for the entire year and then there are 12 additional sheets, one for each month. Right now we are entering data into the corresponding month, then selecting the data entered and copying and pasting into the sheet with all orders for the year. I want to make it so that each time new data is entered into one of the individual months, it is automatically added to the sheet with all of the orders for the year. Basically, I want to make the sheet with all orders the master sheet and I want that master sheet to update automatically using the next empty row when data is entered into an individual month's sheet. I also want to make sure that no duplicates are showing up in the master sheet. I am assuming that each individual month sheet will need a VBA code written in order to do this and I am brand new to VBA codes. 

I would be more than happy to upload the file but I have not figured out how to do that yet.


Windows / Chrome 104.0.0.0

7 replies

vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 10, 2022 at 01:35 AM

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.

0
buffala77 Posts 4 Registration date Tuesday August 9, 2022 Status Member Last seen August 11, 2022
Aug 10, 2022 at 07:53 AM

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

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 10, 2022 at 08:33 AM

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.

0
buffala77 Posts 4 Registration date Tuesday August 9, 2022 Status Member Last seen August 11, 2022
Aug 10, 2022 at 09:21 AM

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? 

0

Didn't find the answer you are looking for?

Ask a question
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 10, 2022 at 08:23 PM

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.

0
buffala77 Posts 4 Registration date Tuesday August 9, 2022 Status Member Last seen August 11, 2022
Aug 11, 2022 at 08:14 AM

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!

0
vcoolio Posts 1362 Registration date Thursday July 24, 2014 Status Moderator Last seen September 29, 2022 250
Aug 11, 2022 at 07:12 PM

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

Good luck with your project.

Cheerio,

vcoolio.

0