VBA - Copying Data to new sheets

rayz211 Posts 1 Registration date Tuesday December 1, 2015 Status Member Last seen December 1, 2015 - Dec 1, 2015 at 05:11 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Dec 2, 2015 at 12:16 AM
Greetings Excel experts,

I'm trying to figure out how to tackle a problem. Basically, I have a master sheet with a bunch of data and I would like to write some code that will copy this data and paste it into newly created sheets based on the data. An example should help explain this better.

Master Sheet

What I would like to code to do is go through this data and create new sheets by the months displayed and then copy the data for those months to these respective sheets.

Jan Sheet

Feb Sheet

Mar Sheet

Thanks in advance for any ideas you may have to accomplish what I'm looking for.


2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Dec 1, 2015 at 08:31 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 259
Dec 2, 2015 at 12:16 AM
Hello Ray,

The following code may help also:-

Sub CreateSheetsCopyData()

Application.ScreenUpdating = False

        Dim ar As Variant
        Dim i As Integer
        Dim LR As Long
        Dim c As Range
        Dim ws As Worksheet
ar = Array("Jan", "Feb", "Mar", "April", "May")
LR = Range("A" & Rows.Count).End(xlUp).Row

For Each c In Range("A2:A" & LR)
        Set ws = Nothing
        On Error Resume Next
        Set ws = Worksheets(c.Value)
        If ws Is Nothing Then
        Worksheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
        End If
  Next c
    For i = 0 To UBound(ar)
         Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar(i)
         Range("B1", Range("J" & Rows.Count).End(xlUp)).Copy Sheets(ar(i)).Range("A" & Rows.Count).End(xlUp)
    Next i
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

It creates the sheets and then copies the relevant rows of data. I don't know how large your dataset is but I have assumed, for now, that it stretches out to Column J. You can change this to suit yourself (line 26 in the above code). I've just added five months in the array for the sake of the exercise (line 11 in the code above).You can add the rest.

Following is a link to my test work book for you to peruse. Let us know how it goes.


I hope that this helps.