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:
- VBA - Copying Data to new sheets
- Vba case like - Guide
- Tmobile data check - Guide
- Sheets right to left - Guide
- Number to words in excel formula without vba - Guide
- Transfer data from one excel worksheet to another automatically - 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.