VBA-code to move master sheet/source data (# of rows change often) based on one column to predefined tabs

[faye_mcknight]

I get an excel file each month that needs to be sorted by location, then subtotaled. It has 11 different locations. Each location's file contents must be sent separately in an email to various recipients. I have been spending a lot of time copying each subtotaled section to a new tab sheet and emailing.

I have found a macro that selects specific tab/sheet(s) to email out, so I would like to keep the tab sheet names as listed, & just move the new contents to them monthly.

The title range/column headings would stay the same, but contents rows vary.... meaning one location may have 10 rows one month and only 5 the next. Any ideas on setting up a Macro/VBA code to move contents to each tab, without changing the coding monthly.

Please advise.

Thanks

Faye

 

[My Aswer Is This]

You say you have a Master sheet.

Is this sheet named "Master"

 

[My Aswer Is This]

How many rows of data are we working with in sheet named Master

Is it 10 or 20 or 100 or more?

 

[faye_mcknight]

"You do not have a sheet named" was the only message I received.....

I then tried your suggestion, & created the new file; it worked but I had extra lines of data; then I added the subtotal to the sheet, and received the same error message as before.

I ended up removing the subtotals from the sheet, and removed the + 1 from the script below.

Lastrowa = Sheets(Cells(i, 9).Value).Cells(Rows.Count, "I").End(xlUp).Row + 1

This is not exactly what I wanted but it will suffice....thanks for your help!

 

[My Aswer Is This]

If you have it working that's great.

I really do not know what subtotal has to do with this.

Are you saying in column I the last row of data has a subtotal formula in it?

 

[faye_mcknight]

No, I am saying I used the Data Subtotal feature, and when doing so it would add the word Total to each location; I ended up doing a quick find replace and ended up solving this issue. I also had a separate Tab called "MailOutlook" that held a macro....so I just added the column headings from the Master sheet to "MailOutlook", and added MailOutlook to the column I on the Master sheet. And did end up putting back in the + 1 to the Macro you sent me.

Now everything is working.

Thanks again for your help!