Another copy from Master to specific sheetsClosed

Question

Hello everyone,
  First post here and I am not, by any means, an excel expert (my husband thinks he is, which is why I'm here, lol). So here is my question:
I run a small  business and have different vendors working in the shop. I have been using a sales log with only one master sheet. I would like to have individual vendor sheets populate when I update the master. I know this question has been asked multiple times but none of the scenarios seemed to fit or were very confusing to me (haha)! I've posted a dropbox link of the file. Any help would be greatly appreciated. Have an awesome day!

Regards,
Teryna

https://www.dropbox.com/s/rkib10ver4xuzfd/Blank%20Vendor%20Sales.xlsm?dl=0

For clarification: B3-B11 is the running total and will remain only on the master. B13 down will be the information transferred to individual sheets. Hope that helps!

vcoolio · Answer

Hello Grunt9800,

Could you clarify some things please:-

- In Column B, what does the "#" symbol actually refer to? Does it refer to the number of items sold or does it refer to the number beside the Vendor's name on the sheet tabs?

- In the Master sheet, would it not be best to have each Vendor's name in the main data set (from B14 on) with the relevant data on the same row for each Vendor without separate tables for each Vendor? Data can be entered for each Vendor as items are sold in any order. A VBA code will separate the data into each individual sheet.

- For a seamless transition for data from the Master sheet to the individual sheets, would you be open to suggestions for a new layout of the Master sheet?

Please advise.

Cheerio,
vcoolio.

grunt9800 · Answer

More than open to suggestions! The # symbol refers to the vendor number. If possible, I would like to keep the names off the item log master sheet and only have them on the running total on top of the master sheet. Am I making any sense? Thanks again!

vcoolio · Answer

Hello Grunt9800,

The following code may be a resolution to your query:-

Sub MoveStuff()
Dim ar As Variant, i As Integer
ar = [{"Adrianna 31","Christina 5","Debbie 2","Diana 15","Lexie 32","Lynette 44","Sarah 28","Terry 59","Teryna 90";"31","5","2","15","32","44","28","59","90"}]
Application.ScreenUpdating = False

For i = 1 To UBound(ar, 2)
      Sheets(ar(1, i)).UsedRange.Offset(2).ClearContents
        With Sheet1
            .AutoFilterMode = False
                With Range("B13", Range("B" & Rows.Count).End(xlUp))
                    .AutoFilter 1, ar(2, i)
                    Range("B13", Range("D" & Rows.Count).End(xlUp)).Copy
                    Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteAll
                    ActiveSheet.AutoFilterMode = False
                End With
Sheets(ar(1, i)).Range("E2").Value = "Total Sales"
Sheets(ar(1, i)).Range("E3").Formula = "=SUM(C4:C50)"
        End With
    Next i

Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "All done!", vbExclamation

End Sub

Following is the link to my test work book based on the sample that you supplied:-

https://www.dropbox.com/s/06wbf6exd67ueo0/Grunt9800%28Master%20sht%20to%20multi%20shts%292.xlsm?dl=0

If you look through the work book, you'll notice some minor changes to your original set out.

You only need to enter data row by row (no need for separate tables) in any order and the code will separate each row into the respective individual sheet. In the main sheet, the range D3:D11 will auto-populate with the total sales from each individual sheet. There are formulae in these cells referencing each individual sheet so you will need to keep the names in Column C (C3:C11) in the order that you see them. Do not clear or delete the contents of cells D3:D11 otherwise you will delete the formulae.

Add or alter data as you wish in the main data set of the main sheet and then click on the "RUN" button to see it work.

I hope that this helps.

Cheerio,
vcoolio.

grunt9800 · Answer

Edit: The following applies to the Dropbox file you linked

It seems that the code is only running for the first 2 entries (31 and 5). I looked through the code and couldn't see an obvious solution. Also, the formatting on the individual sheets differs between sheets. Again 31 and 5 seem to be working perfectly. Thanks again, for your help and patience.

vcoolio · Answer

Hello Grunt9800,

I've just tested it all again, both from the link in my last post and my personal file, and all is working as it should, very smoothly, so I can't see why the sample from the same link would not work for you also.

Did you instead copy/paste the code to your actual work book and found that its not working there?

If so, as I mentioned in my last post, I made some minor changes to the work book sample that you supplied because the set out was not conducive to a seamless transition from the main sheet to the individual sheets.

Cheerio,
vcoolio.

P.S. I just now tested the sample at the link I supplied from a different IP address and all is working just fine.

grunt9800 · Answer

Well for whatever reason, I can't get it to work. I used your example file as well as using the code on the original. I'll continue to work on it. Thanks again for your help

vcoolio · Answer

Hello Grunt,

Are you using a Mac or recently upgraded to Excel 2016?

A relative of mine who lives 12,000Km away last night tested the file at the DropBox link and it worked just fine for him also.

You may want to check all your file settings.

Cheerio,
vcoolio.

grunt9800 · Answer

Hello,

  I have Excel 2013. Are there any specific file settings I should be looking for? I have macros enabled.

Thanks again

vcoolio · Answer

Hello Grunt,

(test in a copy of the work book at the DropBox link).

As for the settings, click on the File tab in the main work sheet (top left) then select Options. Next, select Trust Centre and then click on the Trust Centre settings button. On the left hand side of the dialogue box that pops up, you will see a list of categories. Go through each one, but mainly:- 

- Macro Settings 
- File Block Settings 
- Protected View 
- External Content 
- Trusted Documents 

and make sure that all defaults are restored.

In the File Block Settings, restore all defaults but after having done this, select "Excel 2007 and later Macro Enabled Work Books and Templates" and click on the box in the Open column to select it. Click OK and try the code again.
Just out of curiosity, in the sample file I supplied at the DropBox link, run the code from the VBA editor rather than the button on the main sheet and let me know whether or not you receive the same result.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Grunt,

Well, that IS weird!

Let's try a few things:-

After you download the sample file, re-assign the macro to the button (right click on the button--->"assign macro"--->select the macro name (MoveStuff) from the dialogue box--->click OK).

                                  or

Create another button and assign the code to that.

                                  or

Create a short-cut key---> right click on the button--->select "Options" in the macro dialogue box--->create your short-cut key.

Let me know how it goes.

Cheerio,
vcoolio.

vcoolio · Answer

Hello again Grunt,

Try the following modified version of the code:-



Sub MoveStuff()
Dim ar As Variant, i As Integer
ar = [{"Adrianna 31","Christina 5","Debbie 2","Diana 15","Lexie 32","Lynette 44","Sarah 28","Terry 59","Teryna 90";"31","5","2","15","32","44","28","59","90"}]
Application.ScreenUpdating = False
Sheet1.AutoFilterMode = False
  
For i = 1 To UBound(ar, 2)
    Sheets(ar(1, i)).UsedRange.Offset(2).ClearContents
         
    Range("B13", Range("B" & Rows.Count).End(xlUp)).AutoFilter 1, ar(2, i)            
    Range("B13", Range("D" & Rows.Count).End(xlUp)).Copy Destination:=Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    
    Sheets(ar(1, i)).Range("E2").Value = "Total Sales"
    Sheets(ar(1, i)).Range("E3").Formula = "=SUM(C4:C50)"
    
    Sheet1.AutoFilterMode = False
Next i

Application.ScreenUpdating = True
MsgBox "All done!", vbExclamation

End Sub

It may work better in Excel 2013.

Download the sample from the DropBox link, replace the code with the above code, assign it to the button and let us know the result.

Cheerio,
vcoolio.

Another copy from Master to specific sheets

11 responses

Similar discussions

Subscribe To Our Newsletter!