VBA Code for adding sheet from range specified in another sheet
Closed
hitrus87
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
-
Jun 27, 2017 at 04:12 PM
hitrus87 Posts 6 Registration date Tuesday June 27, 2017 Status Member Last seen July 3, 2017 - Jul 3, 2017 at 06:09 AM
hitrus87 Posts 6 Registration date Tuesday June 27, 2017 Status Member Last seen July 3, 2017 - Jul 3, 2017 at 06:09 AM
Related:
- VBA Code for adding sheet from range specified in another sheet
- Google sheet right to left - Guide
- Mark sheet in excel - Guide
- Little alchemy cheat sheet - Guide
- Windows network commands cheat sheet - Guide
- Battery reset code - Guide
3 responses
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!
I will help, if you post your code. I do not provide turn key solutions!
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.
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
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
hitrus87
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
Jun 28, 2017 at 06:16 PM
Jun 28, 2017 at 06:16 PM
/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?
hitrus87
Posts
6
Registration date
Tuesday June 27, 2017
Status
Member
Last seen
July 3, 2017
Jul 3, 2017 at 06:09 AM
Jul 3, 2017 at 06:09 AM
ohk thnks will see this if it works out for me.
Jun 28, 2017 at 06:10 PM