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

 

[mezzopiano1423]

Hi all, I'm finding the code in this thread hugely helpful.

However, I'm getting a lot of run-time 1004 errors. I can't quite determine why - it works some times, not others.

I find that it usually works when right click the active sheet in VBA editor, add a new module, paste in the code, and go from there. Is this std. operating procedure for every macro? (I'm a newbie). I'm wondering why it won't work after multiple tries a module that has been already opened.

Any input is appreciated! Thanks

 

[VoG]

The code has to go in a regular module. Press ALT + F11, Insert > Module then paste in the code.

 

[blarb]

Hello, I found this code very helpful, but was wondering how to modify it so that it automatically uses column A for the extract instead of prompting with the input box. I eventually need this to be part of something that runs on a schedule, when I might not be at my computer. I tried messing with the line:

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

But had no luck. I'm using the second code from page 1. Any help is greatly appreciated!

Thanks
</pre>

 

[VoG]

Hello, I found this code very helpful, but was wondering how to modify it so that it automatically uses column A for the extract instead of prompting with the input box. I eventually need this to be part of something that runs on a schedule, when I might not be at my computer. I tried messing with the line:

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

But had no luck. I'm using the second code from page 1. Any help is greatly appreciated!

Thanks

Try this

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet, iCol As Integer, t As Date
iCol = 1
t = Now
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:=Cells(2, iCol), Order1:=xlAscending, _
        Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    iStart = 2
    For i = 2 To lastrow
        If .Cells(i, iCol).Value <> .Cells(i + 1, iCol).Value Then
            iEnd = i
            Sheets.Add after:=Sheets(Sheets.Count)
            Set ws = ActiveSheet
            On Error Resume Next
            ws.Name = .Cells(iStart, iCol).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
MsgBox "Completed in " & Format(Now - t, "hh:mm:ss.00"), vbInformation
End Sub

 

[helpexcel]

VoG this code is great and I have been using it a number of different ways. However, I recently discovered that it will not copy an item to a new sheet if the unique value only shows up once. It will only copy the if there are 2 or more instances. I'm still fairly new to all of this, and I have been playing around with the code to no avail.

Thoughts??

Thanks!!

 

[VoG]

Sorry, I don't understand why that would be the case.

 

[YasarShahzad]

Hi VoG,

Thanks for the great share, I am having trouble and I would appreciate if you can help me. When I run this it's showing compile error syntax error and first and last row is red.

can you please help me

 

[gunjitc]

Vog:

Thanks for the code, it works brilliantly. I am trying to do the following: After I run the code for the first time, it creates worksheets based on the filter criteria from the master sheet. If there are additional rows that get updated in the master sheet and I run the code again it doesn't append data to existing "filer labelled" worksheet. How can i achieve that?
For example: One of my worksheet is named "U1" and contains data for the filter U1 and contains 50 rows. Now, when a 51st row gets added in the master data sheet, and i run the vba code, it should append that 1 row to the already existing sheet U1.

Could you please help me with this if possible?
Much appreciated

 

[jsotola]

just as a sidenote

here is code that shows execution time in fractions of second


t = Timer
For i = 0 To 10000
' do nothing
Next i
MsgBox "time taken (seconds) : " & Timer - t

 

[COCONNELL]

Hello VoG, Just so you know, I registered entirely for the purposes of making the following comment: This is amazing, I am so grateful that there are people like you out there who put the time and effort into helping otherwise helpless people like me. This code, which I never could have learned on my own, has literally saved hours of my life. I realize it was probably hardly any effort on your part at all, but to me it feels like magic. I see this was posted years ago, but I am certain I speak for myself and the countless others who have come across this and felt as if they discovered a magic genie who could grant their excel based wishes. WE THANK YOU.