copying rows to NEW sheet and naming sheet value of Cell A

[GS7CLW]

Hope everyone is having a better day than yesterday (aggg Mondays);>(

I am working on a new project -- this will include some stuff I already know (emailing the new pages). Am having trouble getting started with the new pages. The WS I am working on has 5 columns and I simply want to scroll through the sheet and copy all the rows with the SAME value in cell A to a new sheet. Column A will always consist of 4 characters, the first 2 are always 5D. Example sheet:

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=308><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1056" width=33><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 832" width=26><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" span=2 width=54><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1920" width=60><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 1984" width=62><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 608" width=19><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 25pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 width=33 align=left>5D2R</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=26>RA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=54>SMRTB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=54>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=60>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=62>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=19>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 align=left>5D2R</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>RA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>COI</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 align=left>5D2T</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>AR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>RG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>11</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>9</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 align=left>5D2T</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>AR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>ADHQ</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD></TR></TBODY></TABLE>

when the macro is done, sheet 1 will be empty and I would have 2 new sheets named

5D2R

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=308><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1056" width=33><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 832" width=26><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" span=2 width=54><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1920" width=60><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 1984" width=62><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 608" width=19><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 25pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 width=33 align=left>5D2R</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=26>RA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=54>SMRTB</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=54>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=60>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=62>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=19>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl64 height=19 align=left>5D2R</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>RA</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>COI</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63>0</TD></TR></TBODY></TABLE>

and

5D2T

<TABLE style="WIDTH: 233pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=308><COLGROUP><COL style="WIDTH: 25pt; mso-width-source: userset; mso-width-alt: 1056" width=33><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 832" width=26><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1728" span=2 width=54><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 1920" width=60><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 1984" width=62><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 608" width=19><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 25pt; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=19 width=33 align=left>5D2T</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 20pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=26>AR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=54>RG</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=54>20</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=60>11</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=62>9</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 14pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=19>0</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=19 align=left>5D2T</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>AR</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>ADHQ</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>5</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>3</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>2</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65>0</TD></TR></TBODY></TABLE>

This is what I have so far, it is not much but I believe it is a start:

'Constants:
'-- 5 columns
'-- cell A's value always 4 digits -- first 2 always 5D
'-- scroll thru the list backwards TO AVOID "REDOING" EMPTY CELLS (A)
'-- when done sheet 1 will be emtpy
For chkConfirmRw = LastNameRow To 1 Step -1
'If the cells in Column A (RSID) are the same select entire row and continue until Column A changes values
     If Range("A" & chkConfirmRw) = ?the same AS THE PREVIOUS CELL? Then
        Range("A" & chkConfirmRw) = select the entire row '(5 columns)
     End If
Next
'take all rows selected (5 columns are a constant) and copy to new page
'
    Range("selectedFROMabove").Select
    Selection.Cut
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Sheets("Sheet2").Select
'name the new sheet the value of Cell A (5D2W, 5D3F etc)
    Sheets("Sheet2").Name = "ValueOFCell-A"
'go back to sheet 1 and continue
    Sheets("Sheet1").Select

ANY help would be greatly appreciated!!

 

[AlisonS]

I wrote something similar a while ago using AutoFilter and AdvancedFilter - AdvancedFilter can get you a unique list of values, eg:

    ActiveSheet.Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("G1"), Unique:=True

Then you can AutoFilter on each one and cut the results to a new sheet.

    Selection.AutoFilter Field:=c, Criteria1:=myCriteria

HTH,
Alison

 

[VoG]

Also see http://www.mrexcel.com/forum/showthread.php?t=396069

 

[GS7CLW]

data pig worked great!

(just installed it to My Documents folder).

Next thing I need to do is loop thru all of the sheets and rename them the first 4 characters of the sheet name. For some reason DATA pig stuck a hypen on the end of each sheet name.

Dim Sheetname
Dim NewSheetName
Dim WB as Workbook
For each Sheet in WB
ActiveSheet.select
NewSheet.name= LCase(Left(SheetName, 4))
Ws.name = NewSheetName
next Sheet

 

[VoG]

Try this

Sub sheetnames()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
    ws.Name = Left(ws.Name, 4)
Next ws
End Sub