auto transfer data from one worksheet to another in the same w'book

[MPEET]

Hi

I get a data extract from a financial database for a couple of years at a time.

The data extract is exported into one excel worksheet. Each extract has many codes. Each code is broken down by 3 sub categories and financial years.

I want to copy the data in the extract and create worksheet naming the worksheets after the codes within the data extract (columnB). Then when these worksheets are created copy the financial data that is to the right (columns e to AH) of column B, column c (sub categories), column d (year) to a defined place in the created worksheet.

I have managed to do this in a half automated way with the code below. What I have to do though is create the worksheets myself and there is a lot! And go down each row of the data extract and press 'ctrl a'

Does anyone have code that would achieve this with a click of a button with comments as I'm pretty new to VBA.

Cheers Michael (my attempt of code below)

Sub aaaPast1()
'
' aaaPast1 Macro
' Macro recorded 31/07/2007 by Michael Peet
'

'v_LE = Application.InputBox("Enter a LE")
'v_Type = Application.InputBox("Enter Type")


'v_tst = Range(v_count).Select

v_crt_row = ActiveCell.Row()
'v_crt_col = ActiveCell.Column()

v_LE_Range = "B" & v_crt_row
v_LE = Trim("LE" & Range(v_LE_Range).Value)

v_ind_Range = "C" & v_crt_row
v_ind = Range(v_ind_Range).Value

v_ind_Range = "D" & v_crt_row
v_year = Range(v_ind_Range).Value

v_Type = v_ind & v_year





'
If v_Type = "ALL2006" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

If v_Type = "ALL2007" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

If v_Type = "KN2006" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If


If v_Type = "KN2007" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

If v_Type = "RN2006" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If


If v_Type = "RN2007" Then

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets(v_LE).Select
Range("D14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If


End Sub


Sub looptst()

v_counter = 10

Range("c&v_counter").Select


'Dim Bcell As Range


'For Each Bcell In Range("B1:B175")

'If Range("Bcell").Value = ""


'Next Bcell

End Sub

Sub tst()

For Each n In Range("B2:B19")

If n.Value = "ALL" Then

Range.Activate

End If

Next n


End Sub

 

[SkipVought]

Hi,

This could probably be done with a minimum of VBA, using MS Query based on the criteria you outlined above.

faq68-5829

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[MPEET]

Yeah MS Query looks a possibility. Does anyone know how to automate fully with VBA? Keen to learn how to do it.

 

[SkipVought]

"Each extract has many codes. Each code is broken down by 3 sub categories and financial years."

Please post a sample of this data and a sample of what you want the REPORT to look like.

I do not recommend chopping the data up into various sheets. Use a pivot table to report on each code.

Like I stated before, it will probably take ZERO VBA code.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Rofeu]

and if you really want it on seperate sheets, use the code as the page field and simply click 'Show Pages'under PivotTable on the PivotTable toolbar.

Cheers,

Roel

 

[MPEET]

Awesome pivot table help, works a treat, still interested in VBA code for learning purposes so I have attached sample data:

COCODE INDICATOR YEAR week 1 amount week 1 count
118 ALL 2006 -17953846.47 191
263 ALL 2006 -207425.23 55
267 ALL 2006 -21518.8 17


LE263 week 1 amount week 1 count

2006 -78,835,611.61 3,230
2007 -117,713,206.34 2,958

KN 2006 -62,149,777.41 1,962
KN 2007 -59,984,219.44 1,591
Split KN/RN % 78.83

RN 2006 -16,685,834.20 1,268
RN 2007 -57,728,986.90 1,367

Cheers

M