+ Reply to Thread
Results 1 to 2 of 2

macro to autopopulate dat + copy over template worksheet with formulas

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    macro to autopopulate dat + copy over template worksheet with formulas

    Hi All, i'm using the macro below to auto populate worksheets with incrementing date it is also copying the first sheet as a template into the other sheets. The one problem is that the templates being copied don't contain the formulas from the original sheet, and there is also one formula in particular which is pulling data from the previous sheet. This formula is:


    here you can see countif sheet1! then countif sheet2! With the marco i have created sheets for 6months, so don't want to keep copying this formula in the next worksheet and manually typing in the next sheet number i.e COUNTIF(Sheet3!$E$8:$E$27,"PSCCROY-"&VLOOKUP(D9,Names!$A$1:$C$39,2,FALSE)&"-*")

    The other formulas in the worksheet are basic so don't look up previous sheets etc, but would like these in the copied over as well as the above issue.


    Sub Dtpopulate()

    Dim newname As String
    Dim dte As Long
    Dim Names As Worksheet

    Set Names = ThisWorkbook.Worksheets("Names") 'variable Names now represents
    'the Temp sheet.
    Application.ScreenUpdating = False

    Worksheets("Template").Cells().Copy 'copy whole Template sheet to clipboard

    'Names.Range("endDate") is the range named "endDate" on the Temp sheet
    'likewise for startDate

    For dte = Names.Range("endDate") To Names.Range("startDate") Step -1
    'dte is dateSerial (daynumber counting from 1-1-1900)

    newname = WorksheetFunction.Text(dte, "dd-mmm-yy")
    Sheets.Add.Name = newname 'Sheets.add returns a worksheet object
    'of which property name is set
    'it is inserted before the active sheet.

    With Sheets(newname).Cells 'paste from clipboard to all cells in new sheet

    .PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With

    Next dte
    Application.ScreenUpdating = True

    End Sub

    Thanks in advance for your help

  2. #2
    Forum Expert
    Join Date
    New York
    MS-Off Ver
    Excel 2003, 2010, 2016 (Windows)

    Re: macro to autopopulate dat + copy over template worksheet with formulas

    Add code like this:

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1