Macro help required please! - Concur V2a.xlsx (0/1)

[S]

Help!

I have a workbook (which I've attached) from which I have to extract
data - every time a field named "ReportID" changes I need to create a
new workbook from a template and copy certain cell contents to the new
workbook.

For example if the first set of ReportID records has three records in
it I need to create one new workbook then copy cells from just this
block of cells.

Then I need it to loop down to the next block of ReportID fields and
do the same thing - so for example if there are twenty records in the
ReoprtID field copy data into one new workbook.

Any help much appreciated.

Thanks in advance

Steve


Sub Concur_Macr_V2a()
'
' Concur_Macr_V2a Macro
'
' Keyboard Shortcut: Ctrl+Shift+C

' Opens document and autofilters on 418251

Application.ScreenUpdating = False

Workbooks.Open Filename:= _

"D:\Data\_Concur\y9GCh4vCsq9j2lhs2sldwsMd2h98hwM8MGhsqhjM(1).xlsx"


Windows("y9GCh4vCsq9j2lhs2sldwsMd2h98hwM8MGhsqhjM(1).xlsx").Activate
Sheets("Pagina1_1").Select
Range("A2").Select
ActiveSheet.Range("$A$2:$AF$242").AutoFilter Field:=15,
Criteria1:="418251"


' Inserts blank line below each ReportID record (Performs a SubTotal
on each change in ReportID)

Selection.Subtotal GroupBy:=6, Function:=xlSum,
TotalList:=Array(14, 32), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A2").Select

' Copies data to a new sheet (Pagina1_2) - this is so that the data
hidden by the AutoFilter is no longer involved

Sheets("Pagina1_1").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pagina1_2"
Range("A2").Select


' code required here to do the copy 'n' paste stuff



Application.ScreenUpdating = True

End Sub

 

[Don Guillett]

Help!

I have a workbook (which I've attached)  from which I have to extract
data - every time a field named "ReportID" changes I need to create a
new workbook from a template and copy certain cell contents to the new
workbook.

For example if the first set of ReportID records has three records in
it I need to create one new workbook then copy cells from just this
block of cells.

Then I need it to loop down to the next block of ReportID fields and
do the same thing - so for example if there are twenty records in the
ReoprtID field copy data into one new workbook.

Any help much appreciated.

Thanks in advance

Steve

 Sub Concur_Macr_V2a()
'
' Concur_Macr_V2a Macro
'
' Keyboard Shortcut: Ctrl+Shift+C

'   Opens document and autofilters on 418251

    Application.ScreenUpdating = False

    Workbooks.Open Filename:= _

"D:\Data\_Concur\y9GCh4vCsq9j2lhs2sldwsMd2h98hwM8MGhsqhjM(1).xlsx"

Windows("y9GCh4vCsq9j2lhs2sldwsMd2h98hwM8MGhsqhjM(1).xlsx").Activate
    Sheets("Pagina1_1").Select
    Range("A2").Select
    ActiveSheet.Range("$A$2:$AF$242").AutoFilter Field:=15,
Criteria1:="418251"

'   Inserts blank line below each ReportID record (Performs a SubTotal
on each change in ReportID)

    Selection.Subtotal GroupBy:=6, Function:=xlSum,
TotalList:=Array(14, 32), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("A2").Select

'   Copies data to a new sheet (Pagina1_2) - this is so that the data
hidden by the AutoFilter is no longer involved

    Sheets("Pagina1_1").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Pagina1_2"
    Range("A2").Select

'   code required here to do the copy 'n' paste stuff

Application.ScreenUpdating = True

End Sub

You cannot attach files to this version of this ng so

Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com