Need help with writting a macro

T

Trowo

New Member
Joined
Dec 15, 2014
Messages
22
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-

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

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
So the indiv. sheets would have the first four columns populated with data from the master sheet.
Click to expand...
I think you meant that columhns E and F from the Master would become C and D on the individual sheets.

Code: 
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
 
Upvote 0
JLGWhiz said:
I think you meant that columhns E and F from the Master would become C and D on the individual sheets.

Code: 
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
Click to expand...

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?
 
Upvote 0
See if this does any better. It will work with data beginning in row 13.
Code: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Macro to compare old prices with new prices for stock and show(-/+)
4 5 6
Replies
58
Views
964
Peter_SSs
Peter_SSs
T
  • Solved
Categorizing and adding data with multiple conditions
Replies
5
Views
573
kevin9999
K
S
  • Solved
best way to get a thick line between rows when data changes.
Replies
4
Views
486
JoeMo
J
A
  • Question Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
304
Abdo
A
A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
152
abdo meghari
A

Forum statistics

Threads
1,226,520
Messages
6,191,532
Members
453,661
Latest member
edfclaya

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top