Macro Help?

[SirQuej]

Hi Guys

I'm looking a Macro to help with a repetitive job I have every month? Below is an example of a spreadsheet I run each month and currently I have to go down the spreadsheet manually and insert two lines between each unique ident, then a single blank line between each date within each ident...I then have to put page breaks between each Ident group....

So this is how it looks when I start:

Ident SurvAddress Date SystRef
1 *************** 01/08/20 *********
1 *************** 01/08/20 *********
1 *************** 02/08/20 *********
1 *************** 02/08/20 *********
2 *************** 01/08/20 *********
2 *************** 01/08/20 *********
2 *************** 02/08/20 *********
2 *************** 02/08/20 *********
3 *************** 01/08/20 *********
3 *************** 01/08/20 *********
3 *************** 02/08/20 *********
3 *************** 02/08/20 *********
4 *************** 01/08/20 *********
4 *************** 01/08/20 *********
4 *************** 02/08/20 *********
4 *************** 02/08/20 *********


And this is how I need it to look:

Ident SurvAddress Date SystRef
1 *************** 01/08/20 *********
1 *************** 01/08/20 *********

1 *************** 02/08/20 *********
1 *************** 02/08/20 *********

Page Break
2 *************** 01/08/20 *********
2 *************** 01/08/20 *********

2 *************** 02/08/20 *********
2 *************** 02/08/20 *********

Page Break
3 *************** 01/08/20 *********
3 *************** 01/08/20 *********

3 *************** 02/08/20 *********
3 *************** 02/08/20 *********

Page Break
4 *************** 01/08/20 *********
4 *************** 01/08/20 *********

4 *************** 02/08/20 *********
4 *************** 02/08/20 *********

The spreadsheet is thousands of lines long so any help would be appreciated.

Thanks

 

[mole999]

just for others, how wide is the data (columns), do they start in A, what are your unique identifiers, currtly it looks like two dates only. Can you have more than two before a page break as that will produce thousands of pages ?

 

[SirQuej]

just for others, how wide is the data (columns), do they start in A, what are your unique identifiers, currtly it looks like two dates only. Can you have more than two before a page break as that will produce thousands of pages ?

Hi Mole999

The first column is column A yes. The columns can be variable widths. The unique identifiers are the Ident column (column A) and the date Column (Column C).

Each unique ident will have a full months worth of dates, so yes, there will be more dates. I simplified it for this exercise. The page breaks only need to be between each unique IDENT, not each date as well..

 

[SirQuej]

Right, so incredibly, I managed to solve this myself (which if you knew my VB skill level, you would be amazed)...

I ended up using TWO separate Macro's...one splits it by ident and inserts the page break...the 2nd one then splits it by date....I've run them and they worked a treat...

Posted below in case anyone is interested....

Sub SplitIdent()
'
' Macro4 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Dim Ident
Dim Index As Integer

For Index = 1 To 10000
Ident = ActiveCell.Value
   ActiveCell.Offset(1, 0).Activate
   ActiveCell.Value = ActiveCell
       If ActiveCell.Value = Ident Then
       Ident = ActiveCell.Value
       ElseIf ActiveCell.Value <> Ident Then
       ActiveCell.EntireRow.Insert
       ActiveCell.EntireRow.Insert
       ActiveCell.Offset(2, 0).Activate
       ActiveWindow.SelectedSheets.HPageBreaks.Add ActiveCell.Offset(-1, 0)
       End If
Next

End Sub





Sub SplitDate()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+b
'

Dim Ident
Dim Index As Integer

For Index = 1 To 10000
Ident = ActiveCell.Value
    ActiveCell.Offset(1, 0).Activate
    ActiveCell.Value = ActiveCell
        If ActiveCell.Value = Ident Then
        Ident = ActiveCell.Value
        ElseIf ActiveCell.Value <> Ident Then
        ActiveCell.EntireRow.Insert
        ActiveCell.Offset(1, 0).Activate
        End If
Next

End Sub