Copy specific row into newly created worksheets

Question

I take a range of cells and create worksheets from a template. I plan on using my template to do the work of copying and pasting work items.

I need to also copy and paste a certain row into the new worksheets.

Sub CreateSheets()
 
Dim rng As Range
Dim cell As Range

On Error GoTo Errorhandling

Set rng = Application.InputBox(Prompt:="Select cell range:", _
  Title:="Create sheets", _
  Default:=Selection.Address, Type:=8)
 
For Each cell In rng

    'Check if cell is not empty      
    If cell <> "" Then
 
        'Insert worksheet and name the worksheet based on cell value
        Sheets("Template").Copy After:=Sheets("Unit Types")

        'Name new sheet based off two cells on Bid Summary List Cells (Bi and Di)
        ActiveSheet.Name = "UNIT-" & cell
    
        'This is where I think I should add the copy/paste lines... but I don't know how.    
        'Copy unit# row and paste in correct worksheet
        Range("XX:XX").Copy Range("XX:XX")
    
    End If

    'Continue with next cell in cell range
Next cell
 
'Go here if an error occurs
Errorhandling:
 
'Stop macro
End Sub

In the pictures I show where I want to paste the information. This way I can link the cells to the appropriate areas in the template and run VBA code to delete blank cells.

Answer 1

Try this:

Sub CreateSheets()
 
    Dim rng As Range, wsUnitTypes as Worksheet
    Dim cell As Range, ws As Worksheet, wb As Workbook
    
    'On Error GoTo Errorhandling 'uncomment this when your code is working...
    
    Set wb = ActiveWorkbook
    Set wsUnitTypes = wb.Sheets("Unit Types")
    
    Set rng = Application.InputBox(Prompt:="Select cell range:", _
                            Title:="Create sheets", _
                            Default:=Selection.Address, Type:=8)
 
    For Each cell In rng.Cells      'loop selected unit#'s
        If Len(cell.Value) > 0 Then 'check if cell is not empty
         
            wb.Sheets("Template").Copy After:= wsUnitTypes
            Set ws = wb.Sheets(wsUnitTypes.Index + 1) 'reference the new sheet
            
            ws.Name = "UNIT-" & cell.Value
            'copy (e.g.) 1x10 range starting at `cell`
            cell.Resize(1, 10).Copy ws.Range("E5")
        End If
    Next cell
 
    Exit Sub 'don't run into the error handler
Errorhandling:
 
End Sub