from MS Access using VBA I want to make a copy of an existing sheet and give the new sheet a specific name (1 Viewer)

abenitez77

Registered User.
Local time
Today, 11:16
Joined
Apr 29, 2010
Messages
141
I have 4 sheets in my workbook and I want to rename the existing 4 sheets and then make a copy of the 4 existing sheets.
I want to have 8 sheets:
2020
2020Rx
2019
2019Rx
2018
2018Rx
2017
2017Rx


This is the code I have so far and it creates the 8 sheets, but I want to create the new ones from copying the existing sheets as it has an image (jpg) that I want to carry over.
'''
Code:
myshtname = "2020"
myshtnameRx = "2020Rx"
For each sheet In loBook.Worksheets
  loBook.Worksheets(sheet.Name).Select
  loBook.Worksheets(sheet.Name).Name = myshtname
 
  set loWorksheetRx = loBook.Sheets.Add
  loWorksheetRx.name = myshtnameRx

  loWorkSheetRx.Activate

  ix = ix + 1
  myshtname = Left(Trim(nAuditdesc2),4) - ix
  myshtnameRx = Left(Trim(nAuditdesc2), 4) - ix & "Rx"
Next Sheet
'''
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:16
Joined
Sep 21, 2011
Messages
14,056
With Excel I always start with the Macro Recording and work off what that creates?
 

Cronk

Registered User.
Local time
Tomorrow, 02:16
Joined
Jul 4, 2013
Messages
2,770
I also use macro recording and customize. Then set a stop point in your code and step through to check that your code is doing what you want.
 

strive4peace

AWF VIP
Local time
Today, 10:16
Joined
Apr 3, 2020
Messages
1,003
hi @abenitez77

here is some code you can use

Rich (BB code):
Sub RenameCopySheets()
  
    Dim i As Integer _
      ,sName As String
  
    'rename first 4 sheets
    For i = 1 To 4
      Sheets(i).Name = CStr(2021 - i)
    Next i
  
    'copy first 4 sheets
    For i = 1 To 4
      sName = CStr(2021 - i)  
      Sheets(sName).Copy after:=Sheets(sName)
      ActiveSheet.Name = sName &  "Rx"
   Next i
  
   MsgBox  "done"
 
End Sub
 

Users who are viewing this thread

Top Bottom