Copying data from one sheet to another

[dhruvgarg]

Hi,

I'm just beginning to VBA and was trying to see if I can set up a macro to help me with the file I'm working on.

I have a master sheet with a list of tasks, categorized based on certain codes ('AZ', 'SC', 'PR' and 'MP'). I want to set up macros so that when I click the button, it copies the data for each category into a new worksheet, and another macro to compile the data back to into the master file.

I've been looking at it as a two step process, one where the data in the master file gets sorted into the four worksheets based on the code, and two where the data gets refreshed in the master file if there are any changes made in the code specific worksheets.

The code I've written to copy the data into the four sheets works but only partially, in that only the first row of the data gets copied and I'm not too sure how to fix that. The following is the code that I've written so far:

Sub AZ()


Dim LastRow As Integer, i As Integer, erow As Integer

LastRow = Worksheets("Master_File").Range("A" & Rows.Count).End(xlUp).Row

For i = 3 To LastRow

If Cells(i, 2).Value = "AZ" Then
Range(Cells(i, 1), Cells(i, 11)).Select
Selection.Copy

Worksheets("AZ").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
End If

Next i
End Sub

Would love any helps and tips regarding this.

[mancubus]

welcome to the forum.

ggiybf here.

here is one solution

Sub test()
'https://www.ozgrid.com/forum/forum/help-forums/excel-general/139918-split-data-into-separate-worksheets-based-on-values-in-column-a
    
    Dim e
    
    With Sheets("Sheet1").Cells(1).CurrentRegion
        .Parent.AutoFilterMode = False
        For Each e In Filter(.Parent.[Transpose(If(CountIf(Offset(B2:B10000,,,Row(1:10000)),B2:B10000)=1,B2:B10000,Char(2)))], Chr(2), 0)
            If Not SheetExists(e) Then Sheets.Add(after:=Sheets(Sheets.Count)).Name = e
            Sheets(e).Cells.Delete
            .AutoFilter 1, e
            .Copy Sheets(e).Cells(1)
            .AutoFilter
        Next
    End With

End Sub



Function SheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    SheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

[dhruvgarg]

Hi ggiybf,

Thanks for the code. I put in the sheet names based on my file and when I ran the code, it creates new sheets for all the categories, but no data is being transferred apart from the headers. How could I adjust that, the data starts in cell (3,1).

[mancubus]

you are welcome.
i am mancubus

giybf is abbreviation of "google is your best friend"
(sorry for duplicate g)

post your workbook pls
(#2 in my signature)

[dhruvgarg]

Hi,

Please find attached a file with the dummy data.

I've tried googling and somehow the solutions don't seem to work for this file.

[mancubus]

a well designed table's topleft cell should be A1.
your table starts at A2. have an extra header at row 1. you can move this to header (insert tab -> text group -> header & footer command)

it seems i have omitted changing the filter column to 2 in original code.

for the current setup ot the sheet, try:

Sub test2()
'https://www.ozgrid.com/forum/forum/help-forums/excel-general/139918-split-data-into-separate-worksheets-based-on-values-in-column-a
    
    Dim e
    
    With Sheets("Master_Plan").Cells(1).CurrentRegion.Offset(1)
        .Parent.AutoFilterMode = False
        For Each e In Filter(.Parent.[Transpose(If(CountIf(Offset(B3:B10000,,,Row(1:10000)),B3:B10000)=1,B3:B10000,Char(2)))], Chr(2), 0)
            If Not SheetExists(e) Then Sheets.Add(After:=Sheets(Sheets.Count)).Name = e
            Sheets(e).Cells.Clear
            .AutoFilter 2, e
            .Copy Sheets(e).Cells(1)
            .AutoFilter
        Next
    End With

End Sub






Function SheetExists(ByVal txt As String) As Boolean
    On Error Resume Next
    SheetExists = Len(Sheets(txt).Name)
    On Error GoTo 0
End Function

[dhruvgarg]

Thank you so much for it!