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

faye_mcknight

New Member
Joined
Jun 19, 2013
Messages
7
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Try this:

Code:
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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"
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top