Results 1 to 5 of 5

Thread: VBA - create multiple duplicate sheets

  1. #1

    Question VBA - create multiple duplicate sheets



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I would like to create multiple copies of a sheet in a workbook, and each sheet will have a particular cell that changes according to a column in a separate sheet.

    For example, sheet 1 contains data in the range A1:A5
    Sheet 2 contains completely separate and fixed information. However, in sheet 2, Cell B2='Sheet 1'!A1
    I would like to create sheets 3,4,5,6,7,8 which are exactly the same as sheet 2, except that B2=A2 in sheet 3, B2=A3 in sheet 4, ect.

    I know how to create duplicate sheets, but i do not know how to write the code to change the formula B2='Sheet 1'!A1 to B2='Sheet 1'!An+1 starting from n=1.

    Any ideas?

    Thanks.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    Post your current code and we'll tweak it.

  3. #3
    Code:
    Sub Copier()
    Dim x As Integer
    
    x = InputBox("Enter number of times to copy active sheet")
    For numtimes = 1 To x
      ActiveWorkbook.ActiveSheet.Copy After:=ActiveWorkbook.Sheets("Sheet1")
    
    Next
    
    
    Dim OriginalText As String
    Dim CorrectedText As String
    Dim i As Integer
    
    
    i = [0,4]
    
    
    OriginalText = Range("F4").Value
    'F4 ='Sheet 1'!Ai
    
    
    CorrectedText = Replace(OriginalText, "i", "i+1")
    
    
    End Sub
    



    This code works to duplicate the sheet, but nothing is replaced..
    Last edited by p45cal; 2016-08-31 at 11:13 AM. Reason: added code tags

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    try:
    Code:
    Sub Copier()
    x = InputBox("Enter number of times to copy active sheet")
    Set SceSht = ActiveSheet
    For numtimes = 1 To x
      SceSht.Copy After:=ActiveWorkbook.Sheets("Sheet1")
      ActiveSheet.Range("B2").Formula = "=Sheet1!A" & numtimes
    Next
    End Sub

  5. #5
    hm thanks!
    Last edited by suesaid; 2016-08-31 at 07:58 AM.

Tags for this Thread

Posting Permissions

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