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]

So you have a sheet named Master

And you want to copy all the rows to another sheet.
So for example on row(12) how would we know what sheet to copy this row to?
Is the sheet name to copy to some place in row(2)

If so you did not say where unless I missed it.

Your subject title says:

based on one column to predefined tabs

But did not say what column
​

 

[faye_mcknight]

The excel file I receive monthly I paste into a Tab called Master, then I sort and subtotal the location column I; the location column I is the criteria. It can have up to 11 different locations in it (NW, NE, SO, etc). I have also created tabs with the location names on them (NW, NE, SO, etc.), these tabs already have the same column headings that are in the master file.

I need all the master sheet data rows, including the subtotal row that corresponds to the specific 11 locations to be copied and pasted into the pre-named location tabs.

Monthly I will copy the entire workbook, and just paste the new file into the Master tab.

Probably be easier to understand if you could see the file; not sure how to share the file on this forum.

 

[My Aswer Is This]

I do not need to see the file.
My question was in what column is the sheet name in.

You said Column "I"


So if column I has George this row is copied to sheet named George
If column I has Bob this row is copied to sheet named Bob

Is that correct.

It seems simple to me. Or am I confused.

 

[faye_mcknight]

That is correct.

 

[My Aswer Is This]

Try this:

Sub Copy_Rows()
'Modified  2/17/2019  7:04:03 PM  EST
Application.ScreenUpdating = False
On Error GoTo M
Dim i As Long
Sheets("Master").Activate
Dim Lastrow As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "I").End(xlUp).Row
Dim Lastrowa As Long
    
    For i = 2 To Lastrow
        Lastrowa = Sheets(Cells(i, 9).Value).Cells(Rows.Count, "I").End(xlUp).Row + 1
        Rows(i).Copy Sheets(Cells(i, 9).Value).Rows(Lastrowa)
    Next
    Application.ScreenUpdating = True
    Exit Sub
M:
MsgBox "You do not have a sheet named" & vbNewLine & Cells(i, 9).Value
Application.ScreenUpdating = True
End Sub

 

[faye_mcknight]

Thanks for taking the time to help me.

I am getting the message "You do not have a sheet named"

Not sure what I need to do?

 

[My Aswer Is This]

The script looks in Sheet Named Master column I for sheet names

If it finds the name Carol but you do not have a sheet named Carol you will get the error code.

Or if you do not have a sheet named "Master"

 

[faye_mcknight]

I don' get it, meaning I understand what you are saying...but I don't understand why it is returning the message as I do have a "Master" sheet along with the tab sheet names, that are also listed in column I. Not one row of data has been copied to any of the tabs.

Is there a way to upload a file in this forum?

 

[My Aswer Is This]

You must have a sheet named "Master"

I would do this:

Create a new workbook

Create a sheet named "Master"

Create a sheet named

Alpha and a sheet named Bravo and a sheet named Charlie

Put these three names in column I of sheet named master

And run the script and see what happens.


You cannot has any value in Column I if it is not a sheet name

I never open files so no need to look at your file.

Does the warning message say

You do not have a sheet named Mary

Or what exactly

If it finds a bad sheet name it should say

You do not have a sheet named Mary or something like this.​

I suspect you have names in Column I that are not sheet names.

These names must be exact