Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    create sheet based column and TEMPLATE sheet

    This macro create a sheet based a value into column of sheet SALDI.
    My problem is: to created the sheet but use for each the sheet TEMPLATE and not the new sheet...
    I have insert into wbook only two new sheet for example... Note: the TEMPLATE is hided...


    Sub CREA_FOGLI()

    Dim i As Long, s As Worksheet

    Set s = Sheets("SALDI")
    i = 3
    While Not IsEmpty(s.Cells(i, 4))
    With ThisWorkbook.Worksheets.Add(after:=Worksheets(Work sheets.Count))
    .Name = Mid((s.Cells(i, 3)), 1, 4) & "-" & s.Cells(i, 4)
    End With
    i = i + 1

    Wend

    s.Activate

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create sheet based column and TEMPLATE sheet

    You already know how to copy a template sheet, see for example <post#=511,096>post 511,096</post: >. You only need to set the Visible property of the copy to xlSheetVisible.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create sheet based column and TEMPLATE sheet

    fired!!!!!!!! The first block of code rename and copy other sheet present into wbook......not TEMPLATE!!!!!!!!!!

    Sub DivideEtImpera()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSourceRow As Long
    Dim lngTargetRow As Long
    Dim strName As String

    On Error GoTo ErrHandler

    Set wshSource = Worksheets("SALDI")
    Set wshTarget = Worksheets("TEMPLATE")
    For lngSourceRow = 3 To wshSource.Range("C65536").End(xlUp).Row
    strName = UCase(Left(wshSource.Range("C" & lngSourceRow), 1))
    On Error Resume Next
    Set wshTarget = Worksheets(strName)
    If Err Then
    Worksheets("TEMPLATE").Copy _
    after:=Worksheets(Worksheets.Count)
    Set wshTarget = Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Name = strName
    wshTarget.Range("G1") = "Letter " & Chr(34) & strName & Chr(34)
    End If
    On Error GoTo ErrHandler
    lngTargetRow = wshTarget.Range("G65536").End(xlUp).Row + 1
    wshSource.Range("A" & lngSourceRow).EntireRow.Copy _
    Destination:=wshTarget.Range("A" & lngTargetRow)
    Next lngSourceRow

    ExitHandler:
    Set wshSource = Nothing
    Set wshTarget = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create sheet based column and TEMPLATE sheet

    You stated that the TEMPLATE worksheet is hidden, so you will have to make it visible. Below the line

    Set wshTarget = Worksheets(Worksheets.Count)

    insert

    wshTarget.Visible = True

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: create sheet based column and TEMPLATE sheet

    Hans i have maked this, but difficult to maintain hiden the TEMPLATE sheet during the macro run:
    Sub CREA_FOGLI_1()

    Dim i As Long, s As Worksheet

    Application.ScreenUpdating = False

    Set s = Worksheets("SALDI")
    ThisWorkbook.Worksheets("TEMPLATE ").Visible = True
    i = 3
    While Not IsEmpty(s.Cells(i, 4))
    ThisWorkbook.Worksheets("TEMPLATE ").Copy after:=Worksheets(Worksheets.Count)
    With ActiveSheet
    .Name = Mid((s.Cells(i, 3)), 1, 4) & "-" & s.Cells(i, 4)
    End With
    i = i + 1
    Wend

    s.Activate
    ThisWorkbook.Worksheets("TEMPLATE ").Visible = False

    Application.ScreenUpdating = True

    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: create sheet based column and TEMPLATE sheet

    You don't have to make the TEMPLATE sheet visible, you should make the copy visible.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •