Automatically select range and copy to new sheet

  • Thread starter Thread starter Alistair
  • Start date Start date
A

Alistair

Hi folks,
I'm trying to produce a catalogue of a few thousand items. Each
item is listed by it's name and has 2 further columns to the right
giving further description and a reference. This list is sorted
alphabetically by it's name.

What I would like to do would be to copy all items whose names
begin with the letter A (with their associated columns) to a sheet
called A, and the same for each letter of the alphabet. Currently I
have to do this by hand each time I make an alteration and it's a bit
of a pain.

In an ideal world I would like to be able to click a button and
this would be done automatically for all items, however a button for
each letter would be OK as I would just have to make sure that I
clicked all the letters I had changed.

Do you think this would be possible in Excel or should I think
about moving to Access? If it is possible then how please?

Thanks in advance,
Alistair
 
Alistair,
Try this Macro

Copy your Database to the sheet1 of a new workbook
delete the other sheets and run this macro

Sub AddSheets()
'This will add sheets to a workbook
'with one sheet & name them A-Z
For i = 1 To 26
Sheets.Add After:=Sheets(i)
Sheets(i + 1).Name = Chr(i + 64)
Next i
End Sub

Run this macro every time you add/delete data to/from your database

Sub FilterToSheets()
'!!Row1 MUST have headers for Advancedfilter to work!!

LR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
Sheets(1).Range("A1:C" & LR).Name = "DataBase"
Sheets(1).Range("F2").Value = Sheets(1).Range("A1").Value
Sheets(1).Range("F2:F3").Name = "Criteria"

Application.ScreenUpdating = False

For i = 1 To 26
Sheets(1).Range("F3").Formula = Chr(i + 96)
Sheets(i + 1).Activate
Sheets(1).Range("DataBase").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Sheets(1).Range("Criteria"), _
CopyToRange:=Range("A1"), Unique:=False
Next i

Application.ScreenUpdating = True

End Sub

HTH
Cecil
 
Hi Cecil,

sorry to be so long replying but my girlfriend sometimes gets upset if
I spend more time on the computer than with her ;-)

I tried your routine and the first section creates the sheets well -
very handy to have at times. But when I ran the second set to copy
filtered results to each sheet then it copied the entire of the data
from Sheet 1 to each sheet.

I do have Row 1 as headers and if I go to Data > Filter > Autofilter
then Row 1 works and offers me dropdown lists for all the data below.
just in case I tried running your routine with Autofilter set up in
this way - but it still copied the entire of Sheet 1 each time.

I don't get any errors when it runs so I assume that all is OK (I did
Copy it straight from here and Paste into a new module in the VB editor
- is this correct?). Not sure if the underscores > _ are correct (the ones before the Action and CopyToRange) or have these
been added in the posting?

Thanks for your help so far but any ideas why this happened?

Ever hopeful,

Alistair
 
Hi,
Did you try Advanced filter manually,
the cells around the criteria range (F2:F3) should be clean,
choose an empty column for the criteria range lets say column K
Copy the header of the Names column and paste it in one cell (K3)
put an "a" in the cell below (K4)
select your data table
Data>Filter=AdvancedFilter>
in the AdvancedFilter dialog box check the list range is correct
Click in criteria range and select the criteria range (K3:K4)
check the Copy to another location radio button
and in copy to area select a cell of a empty column say (N1)
and click OK.
and tell us whether it works.
Cecil

Alistair said:
Hi Cecil,

sorry to be so long replying but my girlfriend sometimes gets upset if
I spend more time on the computer than with her ;-)

I tried your routine and the first section creates the sheets well -
very handy to have at times. But when I ran the second set to copy
filtered results to each sheet then it copied the entire of the data
from Sheet 1 to each sheet.

I do have Row 1 as headers and if I go to Data > Filter > Autofilter
then Row 1 works and offers me dropdown lists for all the data below.
just in case I tried running your routine with Autofilter set up in
this way - but it still copied the entire of Sheet 1 each time.

I don't get any errors when it runs so I assume that all is OK (I did
Copy it straight from here and Paste into a new module in the VB editor
- is this correct?). Not sure if the underscores > _ are correct (the ones
Click to expand...
before the Action and CopyToRange) or have these
 
Back
Top