Macro: Create Sheets, Copy Data According To a Cell Value

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Why not use [af]*[/af] or [advf]*[/advf] or [pt]*[/pt]

Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds

Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.

Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.

Create Worksheets for Each Item in an Excel Table of Data I'm often asked how one could create x number of Worksheets where each one houses the data specific to each item in a table. The task at hand is to create 1 Worksheet for each item (named as the item) and have all associated data on it.

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

That's the idea behind [pt]*[/pt]

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Why bother a PivotTable will display what you want without creating unnecessary sheets?

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Sharan

Wrong.

It can be automated.

I have advanced filter code that will filter to

New worksheets.

New workbooks.

Existing worksheets.

Existing workbooks.

Worksheets with existing data.

etc

The only problem with your data is the lack of a headers/field names.

Oh, wait a minute I wonder what this file does?

AdvFilterNoFieldsToTemplateWB.xls

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Sharan

You need headers to filter.

I don't have time to write code for your actual data but I'll attach the workbook I mentioned.

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Sharan

I didn't say the code would work with your data or even run without error.

It's an example - I thought you could maybe study it and see if you could adapt it for your needs.

It's a bit much to straight out ask for a complete solution.:)

I could probably give you one but at the moment I don't have the time.

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Hi Sharan,

Let me see if Ive got this right you want to create a new worksheet for each split time, named after the split time. Then for EACH split time copy the data onto the appropriate sheet.

Sam

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Quote from tosharan

I Agree that sir... i am sorry if i have written something wrong...i will surely study that.. however if you could get me the actual code... i would be greatfull to you..

thanks
Sharan

He gave you the code in the example workbook attached to his post...that is the only code he is going to give to you because he doesn't have time to DO IT FOR YOU.

So...like you said..."i will surely study that"...well...maybe you should STUDY the code he attached for you as an example...

Re: Macro To Create Work Sheets And Copy Data According To Cell Value In Main Sheet

Its messy and not very methodical but hey if it works why not.

So Try This:

Sub CreateSplitTimeSheets()
    Dim OneCell As Range
    Dim TwoCell As String


    Application.ScreenUpdating = False


    With Worksheets("Sheet2") ' replace with your main sheet containing split times
        For Each OneCell In .Range("F1", .Range("F65536").End(xlUp))
            If Application.WorksheetFunction.Text(OneCell, "HHMM") <> TwoCell Then
                Sheets.Add
                ActiveSheet.Name = Application.WorksheetFunction.Text(OneCell, "HHMM") & " Hrs"
                For Each Cell In .Range("F1", .Range("F65536").End(xlUp))
                    If Application.WorksheetFunction.Text(OneCell, "HHMM") = _
                       Application.WorksheetFunction.Text(Cell, "HHMM") Then
                        With ActiveSheet
                            .Select
                            If IsEmpty(.Range("F65536").End(xlUp)) Then
                                .Range("F65536").End(xlUp).Select
                            Else
                                .Range("F65536").End(xlUp).Offset(1, 0).Select
                            End If
                            Selection.NumberFormat = "h:mm;@"
                            Worksheets("Sheet2").Range(Cell.Offset(0, -5), Cell.Offset(0, 36)).Copy
                            Selection.Offset(0, -5).Select
                            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                    :=False, Transpose:=False
                        End With
                    End If
                Next Cell
            End If
            TwoCell = Application.WorksheetFunction.Text(OneCell, "HHMM")
        Next OneCell
    End With


    Worksheets("Sheet2").Select ' replace with your main sheet containing split times
    Range("A1").Select
    Application.ScreenUpdating = True


End Sub

Regards,
Sam