Macro Help?

SirQuej

New Member
Joined
Feb 2, 2018
Messages
12
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 2, 2018
Messages
12
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

New Member
Joined
Feb 2, 2018
Messages
12
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....

VBA Code:
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
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,123,304
Messages
5,600,862
Members
414,409
Latest member
FloordAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top