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
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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:

faye_mcknight

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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:

faye_mcknight

New Member
Joined
Jun 19, 2013
Messages
7

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,872
Office Version
  1. 2013
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,109,436
Messages
5,528,749
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top