Excel macro help - Create Sheets/Copy Data

Excel Excel 2007
February 9, 2010 at 10:16:41
Specs: Windows XP
Excel noob needs help. I have a spread sheet that changes daily. I would like a macro that will search worksheet named "Schedule Report" column G and create new sheet for each city listed and copy the row to new sheet. file today may have 10 cities and next day may be 20

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


See More: Excel macro help - Create Sheets/Copy Data

Report •


#1
February 9, 2010 at 11:27:57
re: "file today may have 10 cities and next day may be 20"

If the macro runs today, it would create 10 new sheets and copy the data based on today's data.

What should happen the next day? Should it just create sheets/copy data for the 10 additional cities or does it also have to copy/change/update data for the original 10 cities?

If it has to work with the data from the original cities, how does that need to be a handled?

What will have changed? Will it just be additional lines, etc.?


Report •

#2
February 9, 2010 at 11:32:49
Thanks for reply. Every day I get a new file. So I was going to put macro in personal.xlsm so macro would be a run once and done.

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


Report •

#3
February 9, 2010 at 11:36:46
If the file has 10 cities, does that mean 10 rows of data or could Column G contain multiple instances of one or more cities?

In other words, could there be 15 rows of data for 10 cities?

Are there header rows?

Is the data sorted in any way?


Report •

Related Solutions

#4
February 9, 2010 at 11:45:48
Column G could contain15 rows of data for 10 cities. and there is a header row. data is sorted by date in column A
I tried to past example but I can not get it to display clearly

Date	WO	Job Id	User	Business	Address	City	Location	Tags	
2/9/2010	1	1	1	1	1	Albany	1	1	
2/9/2010	2	2	2	2	2	Buffalo	2	2	
2/9/2010	3	3	3	3	3	Chicago	3	3	
2/10/2010	4	4	4	4	4	Buffalo	4	4	
2/11/2010	5	5	5	5	5	Albany	5	5	
2/12/2010	6	6	6	6	6	Detroit	6	6	

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


Report •

#5
February 9, 2010 at 12:44:22
There are many ways to accomplish your goal...

Try this in a back-up copy of your workbook in case something goes terribly wrong.

The first thing it does is create a list of unique cities in Column J to use for the sheet creations. It will delete that list after the sheets are created.

If Column J isn't a good place for the temporary storage of the list, pick a better spot.

Option Explicit
Sub CitySheets()
Dim lstRow
Dim City
Dim numCities
Dim nxtRow
Dim nxtCity
 With Sheets("Schedule Report")
 'Create List of Unique City Names in Column J
   numCities = .Range("G" & Rows.Count).End(xlUp).Row
    .Range("G1:G" & numCities).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=.Range("J1"), Unique:=True
'Determine How Many Cities Are In The List
   lstRow = .Range("J" & Rows.Count).End(xlUp).Row
'Loop through Column J Creating Sheets and Copy Header Row
   For City = 2 To lstRow
    Sheets.Add after:=Sheets(Sheets.Count)
     ActiveSheet.Name = .Range("J" & City).Value
      .Rows(1).EntireRow.Copy _
       Destination:=ActiveSheet.Range("A1")
    Next
 'Delete List In Column J
    .Range("J1").EntireColumn.ClearContents
 'Loop through Column G and copy Rows to Sheet Based On City Name
    For nxtCity = 2 To numCities
      nxtRow = Sheets(Range("G" & nxtCity).Value).Range("A" & Rows.Count).End(xlUp).Row + 1
       .Rows(nxtCity).EntireRow.Copy _
        Destination:=Sheets(Range("G" & nxtCity).Value).Range("A" & nxtRow)
    Next
 End With
End Sub


Report •

#6
February 9, 2010 at 13:20:46
Thank you. It creates all the sheets and copies header row and then I get an error
Run-time error'1004'
Application-defined or object-defined error at the below line

nxtRow = Sheets(Range("G" & nxtCity).Value).Range("A" & Rows.Count).End(xlUp).Row + 1

I am leaving work now because of snow storm and I will work on this from home. Thank you very much for all of the work you have done. I am very appreciative

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


Report •

#7
February 9, 2010 at 13:26:28
I found what that error was from it was caused by a blank cell in column G. now I get run-time error '9'
Subscript out of range. and this time I am leaving for the day. thanks again

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


Report •

#8
February 9, 2010 at 15:27:40
Yes, a blank cell in Column G will produce a Subscript Out of Range error since at that point it is looking for a sheet name that doesn't exist.

Since your example didn't show any blank cells I didn't account for them.

You can try modifying this section in this untested manner:

'Loop through Column G and copy Rows to Sheet Based On City Name
    For nxtCity = 2 To numCities
     If .Range("G" & nxtCity) <> "" Then
      nxtRow = _
       Sheets(Range("G" & nxtCity).Value).Range("A" & Rows.Count).End(xlUp).Row + 1
       .Rows(nxtCity).EntireRow.Copy _
        Destination:=Sheets(Range("G" & nxtCity).Value).Range("A" & nxtRow)
     End if
    Next

The code could probably use some more error checking, but since I can't see the entire layout from where I'm sitting, it's hard to guess at the various errors that might occur.


Report •

#9
February 11, 2010 at 05:49:26
Thank you. Sorry for late reply I was stuck in blizzard and went sled riding instead of working. tested it today andit does what I need. thank you again

"Don't hit at all if you can help it; don't hit a man if you can possibly avoid it; but if you do hit him, put him to sleep."
Theodore Roosevelt New York City, February 17, 1899


Report •


Ask Question