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 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 2, 2015 at 12:16 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Dec 2, 2015 at 12:16 AM
Related:
- Mar sheet codes
- Cs 1.6 codes - Guide
- Hitman 3 cheat codes - Guide
- Sheet right to left in google sheet - Guide
- Windows network commands cheat sheet - Guide
- Little alchemy cheat sheet - Guide
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
Dec 1, 2015 at 08:31 PM
Seems to me it is very much like this one
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Dec 2, 2015 at 12:16 AM
Dec 2, 2015 at 12:16 AM
Hello Ray,
The following code may help also:-
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.
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.



