Auto extract data & inserts rows additional rows automatically

[Meeru]

I have the following data from scala:-

Account No. Cost Center Desc. Jan Feb ........ Dec
54000 100 salary 2,500 3000 ........ 4000
54001 100 allowance 3000 4000 .........5000
54002 100 housing 2000 5000.......6000

I would like to extract the data in another worksheet as follows:-

Row# Payroll Related ( heading)
2 Account No. Cost Center Desc. Jan Feb
......... Dec
3 54000 100 salary 2,500 3000
......... 4000
4 54001 100 allowance 3000 4000
..........5000
5 54002 100 housing 2000 5000
.........6000
6 Total 7500
12000 ...... 15000
In this worksheet i have a sum total on row 6, but in next month when i
extract data from scala, what if i have more account no., i would like that
the information is auto extracted with the account no. with the respective
months amount and would automatically add it in rows 6, 7 and then the sum
total is automatically shifted to row 8 without manually inserting additional
rows.

Need your kind help.
thanks
meeru

 

[Billy Liddel]

Meeru

You can do this in the same sheet sorting the data on Cost Center and then
by Account No and applying subtotals to the data.

However, here is a macro that copies the data from sheet 1 to sheet 2 and
performs the operations in sheet 2. If you have not used a macro before this
is what you do. In the workbook:
0. set the security levels to medium or low (Tools, Security)
1. Press ALT + F11 (to open the VB Editor)
2. Choose Insert, Module
3. Paste the macro into the module Theis begins with Sub and ends with End Sub
4. Close the VB editor (ALT + Q)

You can link the macro to a command button so that you can run it whenever
you want. You will want the button on the first sheet.

1. Choose, View, Toolbars, Forms
2. the 4th button on the toolbar is a commandbutton - Click this
3. draw the command button by holding down the left mouse button and
dragging both left and down.
4. you will see the CreateSubtotals macro in the Assign Macro list box -
Click this then click OK
5. Right click the command button and change the label

Click the button to run the macro

You will need to save the workbook. If you are using Excel 2007 then use the
Save As form and save it as an XLMS file.

The code is:

Sub CreateSubtotals()
Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim rng2Copy As Range
Dim rngDest As Range
Dim addr As String, addr2 As String

Set wks1 = Sheets("Sheet1") 'if sheet1 is named differently change name in
quotes
Set wks2 = Sheets("Sheet2") ''if sheet2 is named differently change name
in quotes

addr = Selection.SpecialCells(xlCellTypeLastCell).Address

Set rng2Copy = wks1.Range("A1:" & addr)


Application.Goto wks2.Range("A2")

With Selection.CurrentRegion
.RemoveSubtotal
.Cells.ClearContents
End With

Range("A1").Formula = "Payroll Related"

rng2Copy.Copy wks2.Range("A2")
Application.CutCopyMode = False

'get the last cell in wks2 and sort data
addr2 = Selection.SpecialCells(xlCellTypeLastCell).Address
Range("A2:" & addr2).Sort Key1:=Range("B3"), Order1:=xlAscending,
Key2:=Range( _
"A3"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase _
:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal

'apply subtotals
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(4, 5, 6,
7, _
8, 9, 10, 11, 12, 13, 14, 15), Replace:=True, PageBreaks:=False,
SummaryBelowData _
:=True
End Sub

 

[Meeru]

Thanks Billy. I tried the macro but it gave me an error.

Is there anyway i could send in the workbook file to you so that you can see
from the whole data. It would be a great help from you. You could get a clear
picture as to what i require.
thanks in advance
meeru

 

[Billy Liddel]

Yes Meeru

Send the file to:

peter_athertonAThotmail.com

do the obvious with the AT

Regards
Peter