Autopopulate sheets from summary page

cmarie88 Posts 1 Registration date Tuesday June 13, 2017 Status Member Last seen June 13, 2017 - Jun 13, 2017 at 03:27 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 19, 2017 at 11:41 AM

I know this question has been asked in varying forms on here and I tried to modify the macros presented to work for my particular spreadsheet but with no luck. I'm wondering if the formatting that I have used for my spreadsheet may be causing some issues but I'm also not familiar with macros.

I have a spreadsheet with the first sheet being a summary page to track overall sales with columns of Customer Name/Sold By/Project Manager/Sale Date/Start Date/Contract Amount/Draws (8 of these with 3 sub columns each)/Total Sales/Total Expenses/Total Profit/Profit Percentage/Comments. I have each month separated out with a filled in row between them and a totals row at the bottom of each month to add the sales/profit/expenses up. I also have a fiscal year total row. What I would like to do is have a separate sheet for the three possible Sold by options (Column B) which are done with a drop down list as well as a sheet each of the Project Managers (4 different options) so that we can track how each of them is doing on their particular projects. I don't necessarily need all of the columns copied but can live with it if it's easiest.

Any help or suggestions would be greatly appreciated!


1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 19, 2017 at 11:41 AM
Hi Cmarie88,

Make sure that the 3 Sold by options and the 4 Project managers have their own sheet already created.

Then run the code below:
Sub RunMe()

For Each cell In Range("B2:B" & Range("B1").End(xlDown).Row)
    Sheets(cell.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
    Sheets(cell.Offset(0, 1).Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Next cell

Application.CutCopyMode = False
End Sub

Have you tried using the filter option? It basically does the same but keeps the data on the same sheet.

Best regards,