VBA - Copying Data to new sheets

Closed
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
1343
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 12, 2022
- 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.

Thanks,
Ray

2 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Dec 1, 2015 at 08:31 PM
0
vcoolio
Posts
1343
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 12, 2022
249
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
  
Sheet1.Select
    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
   [A1].AutoFilter
 
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.

https://www.dropbox.com/s/z01ra159vpwe96s/Rayz221.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.
0