VBA Code for adding sheet from range specified in another sheet

Closed
Report
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
-
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
-
Hi Everyone,

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.

3 replies


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!
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017

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.
OK, you caught me in a very rare mood.

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 = ""

ThisWorkbook.Worksheets(FromWhatSheet).Select
ActiveSheet.Range("A" & FromWhatRow).EntireRow.Select
Selection.Copy
PasteToSheet (ToWhatSheet)

End Function

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
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017

/can you give me the workbook or the end result of it?

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?
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017

ohk thnks will see this if it works out for me.