I am not well versed with VBA.
I m looking a code if any one can help.
I have a fixed template in sheet"4" which contains the name of person in cell B4
I want a macro which can auto create the sheet keeping the format and template fixed.
But the sheet name would be from range column E:E in data sheet as well as the Cell B4 of every sheet will indirect to the name of next person in column A:A
few cells can be empty and i dont want sheets for them and it would stop when there is no integer mentioned in the column e of data sheet.
Thanks in advance.
for any clarification please drop a message.
Related:
VBA Code for adding sheet from range specified in another sheet
You don't need a script, you need to record a macro that copies a tab! Record a macro, and edit it to see how the system treats it. Then, change the statis items to variables to make it do what you want.
I will help, if you post your code. I do not provide turn key solutions!
recording is not possible since renaming will have to loook he values in range column and if any value is there it would create a sheet with that number. secondly the number is tagged or is corresponding to the name of individual.
See the below and make it work for your instance. I am not prepared to make changes to this, but this is an example to reverse engineer how I did it, and make your own!
Using the above image as a reference, cut and paste this code block into your own workbook.
Function CountRows(WhatSheet) Dim cellcount cellcount = 0 ThisWorkbook.Worksheets(WhatSheet).Select cellcount = Cells(ThisWorkbook.Worksheets(WhatSheet).Rows.Count, 1).End(xlUp).Row CountRows = cellcount
End Function
Sub PasteToSheet(WhatSheet) Dim tmpRow tmpRow = CountRows(WhatSheet)
ThisWorkbook.Worksheets(WhatSheet).Select Cells(tmpRow, 1).EntireRow.Select Selection.Insert Application.CutCopyMode = False End Sub
Function SheetToSheet(FromWhatSheet, FromWhatRow, ToWhatSheet) Dim tmpCellValue tmpCellValue = ""
Function CheckIfIsSheet(SheetName) As Boolean CheckIfIsSheet = False For Each ws In Worksheets If SheetName = ws.Name Then CheckIfIsSheet = True Exit Function End If Next ws End Function
Private Sub CreateTheSheet(NamedWhat) Dim Works As Worksheet With ThisWorkbook Set Works = .Sheets.Add(After:=.Sheets(.Sheets.Count)) Works.Name = NamedWhat End With End Sub
Sub CreateBook(WhatSheet) Dim Mainsheet Dim RowCount Dim CurrentRow Dim StartingRow Dim WhatRow Dim CellValue Dim CellToCheck Dim sheetTAB As Worksheet StartingRow = 2 'Change this value to whatever row your master sheet starts on Mainsheet = WhatSheet ThisWorkbook.Worksheets(Mainsheet).Select RowCount = CountRows(Mainsheet) ' MsgBox (RowCount) 'unREM for Feedback 'Now need to check if there is any info on the master sheet If CurrentRow > RowCount Then MsgBox ("No Entries on Worksheet " & Mainsheet & ". Now closing!") 'Upon finding no info, it closes Exit Sub End If 'Now we need ot loop through each line and see if we have a worksheet For CurrentRow = StartingRow To RowCount CellToCheck = "B" & CurrentRow CellValue = ThisWorkbook.Worksheets(Mainsheet).Range(CellToCheck).Value MsgBox (CellValue) 'unREM for feedback 'need to see if the tab exist, if not create it doesSheetExist = CheckIfIsSheet(CellValue) MsgBox (doesSheetExist) If doesSheetExist = False Then CreateTheSheet (CellValue) End If nret = SheetToSheet(Mainsheet, CurrentRow, CellValue) ThisWorkbook.Worksheets(Mainsheet).Select Next End Sub
Have fun!
P.s.
The above code is as is, and we are hardly responsible for deploying the above code into your environment, and what may or may not happen!
It's kind of fun to do the impossible! -Walter Elias Disney
No I cannot give you the book. I will however describe to you that the above code produces four tabs, named george fred mary and brad, along with the main sheet1. It then copies each line to its appropriate tab based on the name. So in other words, mary data gets copied to mary sheet! brad data gets put on brad sheet. George data gets deposited onto George sheet, and you can guess where freds data goes, cant you?
