HI, i have a list of unique names in a workbook and want to create new new worksheet according to those names. the list of unique names are in sheet1 in a column to .other other thing is that .there is data on that those names which also to transferred to that respective sheet.
i have to do the same procedure daily so wanted to have vba coding for that which automatically create new worksheet and also transfer data on those particular sheet.
one more thing that data which is once tranferred should not be deleted and new data be pasted below the data on respective sheet.supoose a new unique name comes it also automatically create new worksheet without deleting the other sheet.
Creating new new worksheet from from a list
-
-
Re: Creating new new worksheet from from a list
Hi and welcome to the forum.
I haven't had time to test this nor to add all of the things that you want but it should help get you started.
Code
Display MoreSub Main() Dim rngToTest As Range, rngUnique As Range, rngCell As Range Dim wsNew As Worksheet Application.ScreenUpdating = False With Worksheets("Sheet1") Rows("1:1").Insert .Range("A1").Value = "Dummy header" 'add header rows for Advanced Filter Set rngToTest = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row) rngToTest.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("G1"), Unique:=True 'copy the unique entries to another range (amend "G1" to suit) Rows("1:1").Delete 'remove header rows Set rngUnique = .Range("G1:G" & .Cells(Rows.Count, "G").End(xlUp).Row) 'establish a reference to the unique range and the range at its right For Each rngCell In rngUnique If Not SheetExists(rngCell.Value) Then Set wsNew = Worksheets.Add wsNew.Name = rngCell.Value 'do stuff End If 'add new sheet if not already present Next rngCell 'loop through the unique range End With Application.ScreenUpdating = True End Sub Function SheetExists(strWSname As String) As Boolean Dim ws As Worksheet On Error Resume Next Set ws = Worksheets(strWSname) On Error GoTo 0 If Not ws Is Nothing Then SheetExists = True End Function
-
Re: Creating new new worksheet from from a list
Hi Jainny
Welcome to Ozgrid. Try the following. Notice I added headers which is important for this method.
Code
Display MoreOption Explicit Sub AdvFilter() Dim ws As Worksheet Dim ws1 As Worksheet Dim wsNew As Worksheet Dim rng As Range Dim lr As Integer Dim myRg As Range Dim lw As Long Set ws1 = Sheet1 Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 5) lw = Range("A" & Rows.Count).End(xlUp).Row ws1.Range("A2:A" & lw).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("T1"), Unique:=True lr = Cells(Rows.Count, "T").End(xlUp).Row Range("V1").Value = Range("A1").Value For Each myRg In Range("T2:T" & lr) Sheet1.AutoFilterMode = False ws1.Range("V2").Value = myRg.Value On Error Resume Next Set ws = ThisWorkbook.Worksheets(myRg.Value) If Err > 0 Then Set wsNew = Sheets.Add wsNew.Move After:=Worksheets(Worksheets.Count) wsNew.Name = myRg.Value Err.Clear rng.AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=ws1.Range("V1:V2"), CopyToRange:=wsNew.Range("A1"), Unique:=False Else Range("A:A").AutoFilter 1, myRg.Value Range("A2:E" & lr).SpecialCells(12).Copy Sheets(myRg.Value).Range("A" & Rows.Count).End(xlUp)(2) Sheet1.AutoFilterMode = False End If Next ws1.Columns("T:V").Delete End Sub
-
Re: Creating new new worksheet from from a list
hi.the sheet is working but there are some problem as the data is not copying in a right way.i was not knowing that header is needed so i didnt pasted but now i have made an header also.i have attached to another sample report.in sheet one i have attached the data that have to be pasted respected sheet on day 1 and on sheet to the data have to pasted on day 2.sheet b is the way i wanted it to be the outcome.
-
Re: Creating new new worksheet from from a list
the sheet also merging two similar name in one sheet only like it is taking BAJAJ and BAJAJHIND same sheet and updating data in bAJAJ sheet.and suppose i change value of column a in sheet 1 of a to aa.it create new worksheet but doesnot change post the data..
-
-
Re: Creating new new worksheet from from a list
hi,there is a problem in the above code as after running the macro, sheets are properly made but the in d sheet dcb is also showing which i dont it to be there.it shld be unique sheet and the data to be transferred should also be of d not of dcb.
-
Re: Creating new new worksheet from from a list
Hi Jainny
Give this file a try. I modified the code slightly.
Take care
Smallman
-
Re: Creating new new worksheet from from a list
hi, hey it still not working.problem is still there.in D sheet data of dcb is still coming.
-
Re: Creating new new worksheet from from a list
Hi
I just ran the report several times and no DCB appeared in the d sheet. I suggest you delete all your sheets and run the procedure from scractch. I think you will find the only thing in the d sheet is d.
Take care
Smallman
-
Re: Creating new new worksheet from from a list
hi,
i am trying to do same thing and have deleted all the sheets and new sheet that u have attached "filtertonewsheets(1)" i ahve downloaded that and had tried but is giving the result.
can u recheck whether the sheet that have been attached has the updated code and if yes then do try add to two new names in column a "bajaj" and "bajaj hind" in sheet1 and ran the report.. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!