Alright. To say I'm new to VBA would be an understatement, so my code below
is undoubtedly full of things that don't even make sense. bare with me here.

Here's the situation - I need to take a workbook with a bunch of sheets with
retailer data and export it to a single retail list in a new workbook.

Every sheet with retail data is formatted identically, so that I know that
cells B2:R2 contain the same headers and the data below them contain the same
types of data. The length of lists on each worksheet is different, and
changes every now and then.

I originally recorded a macro that simply selected each sheet by name and
collected the data and dumped it into a new workbook. As time goes on, I'm
getting tired of updating the macro each time I create a new sheet with new
retailers on it, so I need to get some VBA in that is a little more
sophisticated - what I visualize is as follows, I just don't know VBA syntax
well enough to make it work.

The first sheet is always called "Notes on this Document", then there are a
bunch of sheets with retailer information, then a sheet called "bulk", which
is formatted differently than the retailer sheets.

I want the VBA to do this:

Create a new workbook,
find the worksheet called "Notes on this document" and activate the next
sheet.
Copy the headers (B2:R2) from this sheet to the new workbook in A1.
Go back to active worksheet on the original workbook and select B3:R3 and
simulate keystroke CTRL+SHIFT+downarrow (this is the simplist way I could
think of to select the whole list not knowing how big it is).
Copy selected range, insert-paste data into A2 of new workbook - this pushes
all the old data down, and you can repeat it as many times as you want
without having to keep track of what else has been pasted.
go back to retailer workbook and go to next worksheet from active worksheet.
Test the name of active worksheet - if it is called "Bulk", go back to new
workbook sort it (details in code below) and then stop.
If it is not called bulk, repeat the copy paste function listed above,
inserting data below A1 of the new workbook, pushing any existing data
downward.

This way, it doesn't matter how many retailer sheets I have, it will just go
to the next one until it finds "bulk", then stops.

The last thing it should do, after it sees "bulk" is sort the new list.

As a side issue, it would be nice if I could identify the new workbook
somehow renaming it, but not saving it to disk. Right now, this button only
works once, then I have to close excel and reopen it to have it work again,
because my VBA switches between the workbooks by looking for "Book1". Not a
big deal, since I'm usually only exporting once in any given session, but it
would be a nice bonus.

Here's the code I've got, which successfully exports the first sheet, but I
don't know how the syntax of an if/then statement well enough to have it test
the next sheet and choose to repeat or go to next step (sort book1 then end).
the following code is a mix of recorded macro, what I've manually typed in
from notes on this page, and a healthy mix of me not knowing what I'm doing.

All comments welcome. Thanks for your time.



Sub Export_storelist()
'
' Export_storelist Macro
' Macro recorded 11/10/2008 by Justin Larson
'

'
Windows("CAL 2009 Sales Tracker.xls").Activate
Sheets("Notes on this Document").Select
ActiveSheet.Next.Select
Range("B2:R2").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
Range("A1").Select
ActiveSheet.Paste

Windows("CAL 2009 Sales Tracker.xls").Activate
ActiveSheet.Select
Range("B3:R3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Book1").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Range("A2").Select
Selection.Insert Shift:=xlDown

Windows("CAL 2009 Sales Tracker.xls").Activate
ActiveSheet.Next.Select
Dim sheet As Worksheet
For Each sheet In Worksheets
If ActiveSheet.Name = "Bulk" Then
End If
Next

Windows("Book1").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:Q1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
End Sub