Using templates and conditional copying and pasting

[husky728]

Hello all, first time poster here so please bear with me.

I have a project where I am trying to write code to be used as a tamplate across many projects. It has 2 sheets (to start). The "Master" sheet, which is where all the information is initially inserted, and the "Hidden" sheet, which is the template the applicable information from the Master sheet needs to be moved too.

Here is what I am trying to do from a fairly high level.

1. User files in information into master sheet.
2. User presses command button
3. VBA searches column B starting at row 14 (running until an empty cell) looking for operation names. For each operation name (many will repeat), create 1 new worksheet named with that operation name (cannot have repeats).
4. Say a new worksheet is named "Operation 1" and on the Master sheet, there are 4 lines relating to "operation 1". From this point, I want the Rows associated with having the condition of "Operation 1" being the operation name, to be copied and pasted into the template ("Hidden") starting at B14 and working down as needed.

The following is what I have so far and I'm having issues with 2 things.
1. I can't get code to skip past repeating values in column B. The first 2 cells are "Op 005" and it makes the first template, but stops from that point with an error for naming the tab (because it is repeating)
2. I'm trying to figure out how to have it copy and paste the rows from the master sheet to the applicable NewSheet (based on operation name).

I am by no means fluent in VBA, I can work through it, but am struggling right now. Any help is truly appreciated! Also, I found most of the code I am using on this site (not sure if I am supposed to show references?? https://stackoverflow.com/questions...rom-a-list-in-a-master-tab-and-populate-a-cel)

Thanks!!

------------------------------------------------------------------------------------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
Dim masterSheet As Worksheet
Dim hiddenSheet As Worksheet
Dim NewSheet As Worksheet
Dim myBook As Workbook
Dim lastRow As Long
Dim i As Long
Dim namesColumn




'Define your workbook - here set as the active workbook, assuming it contains masterSheet and hiddenSheet
Set myBook = ActiveWorkbook


'Define your worksheets - The sheets are named "Master" and "Hidden" respectively
Set masterSheet = myBook.Worksheets("Master")
Set hiddenSheet = myBook.Worksheets("Hidden")


'Names are on the Master sheet in column B (2)
namesColumn = 2


'Find the last row of the sheets list
lastRow = masterSheet.Cells(masterSheet.Rows.Count, namesColumn).End(xlUp).Row


'Cycle through the list - Assuming the list starts in column "A" from the 2nd row
For i = 14 To lastRow
    With myBook
        'New sheet
        Set NewSheet = .Worksheets.Add(After:=.Worksheets("Master"))
    End With


    'Find name of the tab and naming the tab
    tabname = masterSheet.Cells(i, namesColumn)
    NewSheet.Name = tabname


    'Copy from hidden template - You can choose the ranges if predefined or use .Cells(r,c) to do something fancier
    hiddenSheet.Range("A1:L62").Copy _
        Destination:=NewSheet.Range("A1:L62")


    'Paste in the new tab
    NewSheet.Cells(1, 1).Value = tabname
    


    'Copy row information from operation to tab with same name on master sheet
    'If masterSheet.Cells("Bi") = NewSheet.Name Then
    'masterSheet.Range("Bi:Li").Copy _
        Destination:=NewSheet


    
Next i


End Sub

 

[husky728]

Well, I see I have several people reading the post hbut no responses, so in case it's useful to someone else I figured it out so my code is below!

Essentially what I am doing (and I admit it is most likely not the most efficient way to do it probably and there is room for error if things are not input to the master sheet appropriately) is looking at the cell before and after the cell with the operation name and determining if I need to create a new worksheet or if I need to add to the last made. For example, for operations 1, 2, 2, 3 it would only create a new sheet for 1, 2, and 3. One the second "2" it would add that info to the worksheet created by the first "2". Issue is if they were inserted as 2, 1, 2, 3 the script would fault our for a renamed sheet.

Private Sub CommandButton1_Click()
Dim masterSheet As Worksheet
Dim hiddenSheet As Worksheet
Dim newSheet As Worksheet
Dim myBook As Workbook
Dim LastRow As Long
Dim i As Long
Dim namesColumn
Dim rngMyRange As Range, rngCell As Range
Dim sht As Worksheet
Dim SheetName As String
R = 0


'Define workbook
Set myBook = ActiveWorkbook


'Define worksheets
Set masterSheet = myBook.Worksheets("Master")
Set hiddenSheet = myBook.Worksheets("Hidden")


'Define which column in your master worksheet the list is
namesColumn = 2


'Find the last row of the sheets list
LastRow = masterSheet.Cells(masterSheet.Rows.Count, namesColumn).End(xlUp).Row


'Cycle through the list
For i = 14 To LastRow

'Check to see if it is a repeating operation name (row)
R = R
If masterSheet.Cells(i, 2).Value = masterSheet.Cells(i - 1, 2).Value Then
GoTo Line_2
ElseIf masterSheet.Cells(i, 2).Value <> masterSheet.Cells(i - 1, 2).Value Then
R = 0
End If

'Start place for new operation or first line of master sheet
Line_1:


With myBook
'New sheet
Set newSheet = .Worksheets.Add(After:=.Worksheets("Master"))
On Error GoTo Line_99

End With


'Find name of the tab and naming the tab
tabname = masterSheet.Cells(i, namesColumn)
newSheet.Name = tabname


'Copy from hidden template and paste into new worksheet
hiddenSheet.Range("A1:L62").Copy _
Destination:=newSheet.Range("A1:L62")

'Set Column width for fitting data
newSheet.Columns(2).ColumnWidth = 20
newSheet.Columns(2).HorizontalAlignment = xlCenter
newSheet.Columns(3).ColumnWidth = 50
newSheet.Columns(4).ColumnWidth = 25
newSheet.Range("C14:C28").HorizontalAlignment = xlLeft
newSheet.Rows(5).AutoFit
newSheet.Rows(6).AutoFit
newSheet.Rows(7).AutoFit
newSheet.Rows(8).AutoFit


'Paste in new tab name
newSheet.Cells(14, 2).Value = tabname

'Paste in machine information
newSheet.Cells(14, 3).Value = masterSheet.Cells(i, 3).Value
newSheet.Cells(14, 4).Value = masterSheet.Cells(i, 4).Value
newSheet.Cells(14, 5).Value = masterSheet.Cells(i, 5).Value
newSheet.Cells(14, 6).Value = masterSheet.Cells(i, 6).Value
newSheet.Cells(14, 7).Value = masterSheet.Cells(i, 7).Value
newSheet.Cells(14, 8).Value = masterSheet.Cells(i, 8).Value

'Skip over detail addition for new operations
GoTo Line_3:

'Start place for repeated operation
Line_2:

'Add a new row for the data
newSheet.Rows(15 + R).Insert

'Copy over operation information
newSheet.Cells(15 + R, 2).Value = tabname
newSheet.Cells(15 + R, 3).Value = masterSheet.Cells(i, 3).Value
newSheet.Cells(15 + R, 4).Value = masterSheet.Cells(i, 4).Value
newSheet.Cells(15 + R, 5).Value = masterSheet.Cells(i, 5).Value
newSheet.Cells(15 + R, 6).Value = masterSheet.Cells(i, 6).Value
newSheet.Cells(15 + R, 7).Value = masterSheet.Cells(i, 7).Value
newSheet.Cells(15 + R, 8).Value = masterSheet.Cells(i, 8).Value

R = R + 1


'Skip over detail addition for new operations
Line_3:


Next i


Line_99:


Application.DisplayAlerts = False
newSheet.Delete


End Sub