VBA - create new sheet and organise data

[Padthelad]

Hi,

I am looking for VBA code to find a solution to a problem.

I have a master workbook with various ‘quote enquiries’. Usual things are included such as name, address, phone number, quote details. I also have a column that has the date a quote was sent (column M).

I would like a Macro that would search through the worksheet and create a new workbook with all the data from that row if column M is empty (quote sent column). Then organize from ‘oldest’ to ‘newest’.

Example of data below.



[TABLE="width: 785"]
<tbody>[TR]
[TD]
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Number
[/TD]
[TD]TB
[/TD]
[TD]Date
[/TD]
[TD]Name
[/TD]
[TD]TB Type
[/TD]
[TD]Tel Number
[/TD]
[TD]Email
[/TD]
[TD]Address
[/TD]
[TD]Source
[/TD]
[TD]Entered By
[/TD]
[TD]Visit Date
[/TD]
[TD]Quote Sent
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1348
[/TD]
[TD]TB
[/TD]
[TD]02/07/2016
[/TD]
[TD]Test1
[/TD]
[TD]Badminton
[/TD]
[TD]12345
[/TD]
[TD]Test1@test
[/TD]
[TD]123 Testing
[/TD]
[TD]Yard
[/TD]
[TD]PD
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1349

[/TD]
[TD]TB
[/TD]
[TD]02/07/2016
[/TD]
[TD]Test2
[/TD]
[TD]Windsor

[/TD]
[TD]6789
[/TD]
[TD]Test2@test
[/TD]
[TD]1234 Testing
[/TD]
[TD]Yard
[/TD]
[TD]PD
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1350

[/TD]
[TD]TB
[/TD]
[TD]03/07/2016
[/TD]
[TD]Test3
[/TD]
[TD]Flimwell
[/TD]
[TD]101112
[/TD]
[TD]Test3@test
[/TD]
[TD]12345 Testing
[/TD]
[TD]Yard
[/TD]
[TD]PD
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1351
[/TD]
[TD]TB
[/TD]
[TD]04/07/2016
[/TD]
[TD]Test4
[/TD]
[TD]Cowbeech
[/TD]
[TD]131415
[/TD]
[TD]Test4@test
[/TD]
[TD]123456 Testing
[/TD]
[TD]Yard
[/TD]
[TD]PD
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

So in summary, I am looking for VBA code that will search the entire worksheet and create a new workbook with all the data from the corresponding row if column M is empty (i.e no quote has been sent). I would like this to then be organised in date order (column D).

I t would be good to be able to run this Macro at the beginning of each day so that I can see any outstanding quotes that need to be sent.

Any help anyone can provide is very much appreciated. I have been looking for various codes for a week now but still can figure what I need.

Thank you in advance.

Pad

 

[Padthelad]

Ahh, right!

OK, so I have changed which column we use to find the "last row" of data from B to C. Try this:

Sub GenerateList()
Dim Cell As Range, cRange As Range
Dim LastRow As Long, LastRow2 As Long
Dim wb As Workbook, wb2 As Workbook

Set wb = ActiveWorkbook

LastRow = wb.Sheets("Main").Cells(Rows.Count, "C").End(xlUp).Row
Set cRange = wb.Sheets("Main").Range("M4:M" & LastRow)

If Application.WorksheetFunction.CountIf(cRange, "") > 0 Then
    Set wb2 = Workbooks.Add
    wb.Sheets("Main").Range("A1:N3").Copy wb2.Sheets(1).Range("A1")
    LastRow2 = wb2.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    For Each Cell In cRange
        If Cell.Value = "" Then
           Cell.EntireRow.Copy
           wb2.Sheets(1).Range("A" & LastRow2).PasteSpecial xlPasteFormats
           wb2.Sheets(1).Range("A" & LastRow2).PasteSpecial xlValues
            LastRow2 = LastRow2 + 1
        End If
    Next Cell
End If

wb2.Sheets(1).Range("D4:D" & LastRow2).Sort Key1:=Range("D4"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

You are my hero Fishboy! Thank you so much!

Pad

 

[Fishboy]

You are my hero Fishboy! Thank you so much!

Pad

Happy to help man