Copy to new worksheets

[lapta301]

Dear All

I have some spreadsheets containing substantial rows of data that come in from our main frame.

I need to copy the rows of data from Sheet1 to new sheets for each office with the sheets named after each office number that is in column H

The one I am currently working on extends from A1 to L2387 but the size changes each time although the sort field is always H. In this ine office 106 has 300 records and office 6300 has 860 records.

I have noticed that there is an apostrophe in front of the number but Excel will sort it properly after asking if I want text that looks like numbers sorted like numbers.

Many thanks

 

[taigovinda]

I feel almost embarrassed to ask this, but... how do you get the list that contains the '8' and descriptors of what all the potential types mean? Is it easily accessible within the help? I've never really used the help but wasn't able to find this offhand - figure I could maybe do a lot more on my own with this bit of knowledge!

Set r = Application.InputBox("Click in the column to extract by", Type:=8)

Thanks,
Tai

 

[VoG]

Hello Tai

See http://msdn.microsoft.com/en-us/library/aa195768(office.11).aspx

 

[taigovinda]

A list of all the methods, and details about their arguments. Perfect. Thanks, VoG!

 

[mrexcelhelp123]

Peter,

Thanks for this code. It's great. However, if I have existing workbooks and want the unique data to populate worksheets within this workbook based on the number I type in a particular cell within this worksheet, how can I do this.

For example:

I have many After.xlsm sheets and I would like data to populate in these sheets depending on the Number I type in a cell. For example, if I type 456 in cell A1 in After456.xlsm then names from Data.xlsx populates automatically in A2, A3, etc. If I then type 758 in cel A1 for workbook After758.xlsm, names from DAta.xlsx would populate in A2, A3 and so forth.

 

[fjdurbin]

Try

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("H2"), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Range("H" & i).Value <> .Range("H" & i + 1).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Range("H" & iStart).Value
            On Error GoTo 0
            ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
            .Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
            iStart = iEnd + 1
        End If
    Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

This is a brilliant piece of code! I'm wondering how it would be modified according to my need. Rather than using a key value to trigger the creation of a new worksheet, I want to trigger a new worksheet based upon the number of rows HOWEVER I don't want to split my key value between multiple worksheets.

For example, I have a csv file with about 200k rows of records with account numbers. 30k rows are account 123456789 and 30k rows are account 987654321 and and 25k rows are account 123455555. Since the rows are sorted I would want the 30k account 123456789 and the 25k 123455555 on sheet1 and the 30k account 987654321 on sheet2.

I hope that makes sense and that you can help me. Thank you...JD

 

[mrexcelhelp123]

hi,

got this error "Method 'Copy of object '_Worksheet' failed". Any idea if this has to do with working in excel 2007.

it appears to be hung up where sh.Copy is:

If sh.Name <> Master Then
sh.Copy
Fname = Folder & "\" & Prefix & sh.Name & ".xls"

 

[VoG]

It worked for me in Excel 2007. You could try .xlsx

 

[ARAARSO]

This really saved me almost 4 hours of work. I was wondering if you could setup so it saves the file as "CSV" file (comma separated file)?

Thank you very much,

 

[taigovinda]

Welcome to the board!

From VoG's code in post #18, try changing the part in red to save csv instead of xls, and delete the part in blue:

        If sh.Name <> Master Then
            sh.Copy
            Fname = Folder & "\" & Prefix & sh.Name & ".xls"
            If Dir(Fname) <> "" Then Fname = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls)", _
                Title:=Fname & " exists - select file to save as")
            ActiveWorkbook.SaveAs Filename:=Fname
            ActiveWorkbook.Close
        End If

 

[Topher916]

Man you guys are all awesome!!!

I have one question...is there a way to name the files as dates? The column range that I am using shows daily dates; file names can be any format of dates.

Thanks again to everyone here and keep posting.