To help explain my situation i've included a zip file with an excel file
called sample.xls.
Sheet1 = the source of data.
Sheet2 = the final data as it should be formated.

I was looking for tips. bits of code ect. anything that can help me.

So the logic should be something like

If column A & B & C & D & E on row 1 = A & B & C & D & E on row 2 then
add the rows in a block for subtotals

If column A & B & C & D & E on row 1 NOT = A & B & C & D & E on row 2
then start the next block for subtotals

I'm pretty sure you can visualize it more easily if you take a look at
the attachement i provided.

Let me know if you have any tips or parts of code to help me.

Thank you!


Here is the code i have so far.
It needs to be greatly improved -.-
--------------------------------------------------------------------------------------------------------
Module 1:
Sub auto_open()
Run open_file()
Run line_insert()
Run smart_totals_headers()
Run save_file()
End Sub

Module2:
'Declarion of global variables

Dim tempwrkdir As Variant
Dim reportwrkdir As Variant
Dim fstrowinblock As Variant
Dim lstrowinblock As Variant

'Function to open the file to be edited

Function open_file()
tempwrkdir = "\\mtldapp01\d-drive\Inetpub\ftproot\AZ\"
Workbooks.Open Filename:=tempwrkdir & "test.xls"
End Function

'Function to save the active file

Function save_file()
reportwrkdir = "\\mtldapp01\d-drive\Inetpub\ftproot\AZ\Primary
Care\"
ActiveWorkbook.SaveAs Filename:= _
reportwrkdir & "new name.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Function

'Function to insert lines, will make room for the smart total headers

Function line_insert()
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Rows("2:2").Select
Selection.Insert Shift:=xlDown
End Function

'Function to format the smart total headers

Function smart_totals_headers()
Range("A7").Select
Selection.Copy
Range("F2").Select
ActiveSheet.Paste
Run concatenate_outlets()
Range("D7").Select
Selection.Copy
Range("F4").Select
ActiveSheet.Paste
Range("E7").Select
Selection.Copy
Range("F5").Select
ActiveSheet.Paste
End Function

'Function to concatenate the outlet code with the outlet name

Function concatenate_outlets()
Range("IV65535").Value = ":"
Range("IV65536").Select
ActiveCell.Formula = "=B7&IV65535&C7"
Range("IV65536").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Function


+-------------------------------------------------------------------+
|Filename: sample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3784 |
+-------------------------------------------------------------------+

--
pops-1
------------------------------------------------------------------------
pops-1's Profile: http://www.excelforum.com/member.php...o&userid=26693
View this thread: http://www.excelforum.com/showthread...hreadid=437403

Nobody has a suggestion/idea or parts of code?


--
pops-1
------------------------------------------------------------------------
pops-1's Profile: http://www.excelforum.com/member.php...o&userid=26693
View this thread: http://www.excelforum.com/showthread...hreadid=437403