Need help with writting a macro

[Trowo]

I am trying to automate a feed that I download into sheet 1 (master) each morning. The feed has none-sense in the first 12 rows but it always comes in that way. Column D has a list of letters that I want to filter by and assign to their own worksheets. I cannot copy the entire row; but rather I want column A to be the same on the separate sheets, column C on master to be column B on indiv. sheets and columns E & F to be columns D & E respectively on the individual sheets. Column B is of no use to me but comes in on the feed and column D is only used in the master sheet for the filter. So the indiv. sheets would have the first four columns populated with data from the master sheet.

Thanks in advance-

A	B	C	D	E	F
ATVI		Activision Blizzard Inc	A	100	$100.00
AIRO		Airspan Networks Inc.	A	200	$400.00
AMZN		Amazon.com, Inc.	A	300	$900.00
AMBR		Amber Road, Inc.	A	200	$800.00
AMCX		AMC Networks Inc. Class A	A	100	$500.00
AMT		American Tower Corporation	A	200	$1,200.00
ANSS		ANSYS, Inc.	A	300	$2,100.00
AZPN		Aspen Technology, Inc.	A	257	$2,057.14
ADSK		Autodesk, Inc.	A	271	$2,442.86
AVID		Avid Technology, Inc.	A	286	$2,857.14
BTN		Ballantyne Strong, Inc.	A	300	$3,300.00
BBGI		Beasley Broadcast Group, Inc. Class A	A	314	$3,771.43
BSQR		BSQUARE Corporation	A	329	$4,271.43
CVC		Cablevision Systems Corporation Class A	A	343	$4,800.00
CDNS		Cadence Design Systems, Inc.	A	357	$5,357.14
ATML		Atmel Corporation	B	371	$5,942.86
AVX		AVX Corporation	B	386	$6,557.14
BELFB		Bel Fuse Inc. Class B	B	400	$7,200.00
BHE		Benchmark Electronics, Inc.	B	414	$7,871.43
BWA		BorgWarner Inc.	B	429	$8,571.43
CAVM		Cavium, Inc.	B	443	$9,300.00
CKP		Checkpoint Systems, Inc.	B	457	$10,057.14
CIEN		Ciena Corporation	B	471	$10,842.86
CRUS		Cirrus Logic, Inc.	B	486	$11,657.14
CSCO		Cisco Systems, Inc.	B	500	$12,500.00
GLW		Corning Incorporated	B	100	$100.00
CRAY		Cray Inc.	B	200	$400.00
DGII		Digi International Inc.	B	300	$900.00
EMAN		eMagin Corporation	B	200	$800.00
EXC		Exelon Corporation	C	100	$500.00
XOM		Exxon Mobil Corporation	C	200	$1,200.00
FE		FirstEnergy Corp.	C	300	$2,100.00
FTI		FMC Technologies, Inc.	C	257	$2,057.14
FST		Forest Oil Corporation	C	271	$2,442.86
GXP		Great Plains Energy Incorporated	C	286	$2,857.14
HAL		Halliburton Company	C	300	$3,300.00
HE		Hawaiian Electric Industries, Inc.	C	314	$3,771.43
HLX		Helix Energy Solutions Group, Inc.	C	329	$4,271.43
NMRX		Numerex Corp. Class A	GM	343	$4,800.00
VZ		Verizon Communications Inc.	GM	357	$5,357.14
APP		American Apparel, Inc.	J	371	$5,942.86
AAT		American Assets Trust, Inc.	J	386	$6,557.14
ACC		American Campus Communities, Inc.	J	400	$7,200.00
MTGE		American Capital Mortgage Investment Corp.	J	414	$7,871.43
AEO		American Eagle Outfitters, Inc.	J	429	$8,571.43
AIV		Apartment Investment and Management Company Class A	J	443	$9,300.00
ABR		Arbor Realty Trust, Inc.	J	457	$10,057.14
ACRE		Ares Commercial Real Estate Corporation	J	471	$10,842.86
AHP		Ashford Hospitality Prime, Inc.	J	486	$11,657.14
AHT		Ashford Hospitality Trust, Inc.	J	500	$12,500.00
MMC		Marsh & McLennan Companies, Inc.	L	100	$100.00
MBWM		Mercantile Bank Corporation	L	200	$400.00
MET		MetLife, Inc.	L	300	$900.00
MS		Morgan Stanley	L	200	$800.00
NYCB		New York Community Bancorp, Inc.	L	100	$500.00
NTRS		Northern Trust Corporation	L	200	$1,200.00
NSAM		NorthStar Asset Management Corp	L	300	$2,100.00
PKBK		Parke Bancorp, Inc.	L	257	$2,057.14
PNC		PNC Financial Services Group, Inc.	L	271	$2,442.86
GIS		General Mills, Inc.	M	286	$2,857.14
THRM		Gentherm Incorporated	M	300	$3,300.00
SJM		J. M. Smucker Company	M	314	$3,771.43
KAR		KAR Auction Services, Inc.	M	329	$4,271.43
K		Kellogg Company	M	343	$4,800.00
KMB		Kimberly-Clark Corporation	M	357	$5,357.14
KRFT		Kraft Foods Group, Inc.	M	371	$5,942.86
KR		Kroger Co.	M	386	$6,557.14
LMNR		Limoneira Company	M	400	$7,200.00
CAH		Cardinal Health, Inc.	P	414	$7,871.43
CFN		CareFusion Corporation	P	429	$8,571.43
CELG		Celgene Corporation	P	443	$9,300.00
CLDX		Celldex Therapeutics, Inc.	P	457	$10,057.14
CERN		Cerner Corporation	P	471	$10,842.86
CRL		Charles River Laboratories International, Inc.	P	486	$11,657.14
CI		Cigna Corporation	P	500	$12,500.00
ETN		Eaton Corp. Plc	S	100	$100.00
ECL		Ecolab Inc.	S	200	$400.00
ESV		Ensco plc	S	300	$900.00
FSS		Federal Signal Corporation	S	200	$800.00
FLR		Fluor Corporation	S	100	$500.00
FMC		FMC Corporation	S	200	$1,200.00

<colgroup><col span="2"><col><col><col><col></colgroup><tbody>
</tbody>

 

[JLGWhiz]

So the indiv. sheets would have the first four columns populated with data from the master sheet.

I think you meant that columhns E and F from the Master would become C and D on the individual sheets.

Sub NewSheets()
Dim sh As Worksheet, lr As Long, c As Range, ns As Worksheet
Set sh = Sheets("master") 'Edit sheet name
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
sh.Range("D1:D" & lr).AdvancedFilter xlFilterCopy, , sh.Range("H1"), True
    For Each c In sh.Range("H1", sh.Cells(Rows.Count, "H").End(xlUp))
        sh.Range("D1:D" & lr).AutoFilter 1, c.Value
        Set ns = Sheets.Add
        sh.Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy ns.Range("A2")
        Range("B1, D1").EntireColumn.Delete
        sh.Range("D:D").AutoFilter
    Next
End Sub

 

[Trowo]

I think you meant that columhns E and F from the Master would become C and D on the individual sheets.

Sub NewSheets()
Dim sh As Worksheet, lr As Long, c As Range, ns As Worksheet
Set sh = Sheets("master") 'Edit sheet name
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
sh.Range("D1:D" & lr).AdvancedFilter xlFilterCopy, , sh.Range("H1"), True
    For Each c In sh.Range("H1", sh.Cells(Rows.Count, "H").End(xlUp))
        sh.Range("D1:D" & lr).AutoFilter 1, c.Value
        Set ns = Sheets.Add
        sh.Range("D2:D" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy ns.Range("A2")
        Range("B1, D1").EntireColumn.Delete
        sh.Range("D:D").AutoFilter
    Next
End Sub

That did not seem to work... not sure if it had something to do with the text or links in the first 12 rows... is it possible to see where I can manipulate the code to match my needs?

 

[JLGWhiz]

See if this does any better. It will work with data beginning in row 13.

Sub NewSheets()
Dim sh As Worksheet, lr As Long, c As Range, ns As Worksheet
Set sh = Sheets("master") 'Edit sheet name
lr = sh.Cells(Rows.Count, 4).End(xlUp).Row
sh.Range("D12:D" & lr).AdvancedFilter xlFilterCopy, , sh.Range("H1"), True
    For Each c In sh.Range("H2", sh.Cells(Rows.Count, "H").End(xlUp))
        sh.Range("D12:D" & lr).AutoFilter 1, c.Value
        Set ns = Sheets.Add(After:=Sheets(Sheets.Count))
        sh.Range("D13:D" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy ns.Range("A2")
        Range("B1, D1").EntireColumn.Delete
        sh.Range("D:D").AutoFilter
    Next
sh.Columns("H").ClearContents
End Sub

This has been tested in a mock up an ran without error producing expected results. The code should be copied to the standard code module 1. See below for accessing code module.
What the code does: It first filters unique codes out of column D and puts that list in column H temporarily. It then uses column H list to sytematically filter all items with a particular code and place that filtered list, entire rows, into a new worksheet. When each item on the list in column H has been filtered and copied, it will clear column H and end the procedure. If the procedure does not complete and an error message is produced, click the 'Debug' button on the dialog box and note the highlighted line of code, then post that information back to this thread.

Also, be sure to check that the sheet name in the code is the same as the actual sheet name.