VBA to copy multiple worksheets and then rename them

Hi
I'm new to the forum and VBA.
I need a command button on a worksheet called "Costings Main Menu" to copy 3 other worksheets and then rename them.
The 3 worksheets to be copied are called "Yearly Lease Order", "Yearly Quote Form", and "Yearly Invoice Form".
I would like the new sheets to be named the same as the sheets they were copied from with the addition of the contents of a cell (C4) in the "Costings Main Menu" worksheet.
So if I typed SIMAT in cell (C4) the new sheets would be named as follows:
"SIMAT Yearly Lease Order", "SIMAT Yearly Quote Form", and "SIMAT Yearly Invoice Form"

This is all contained within the same workbook
Any help with this will be very much appreciated

Thanks

Welcome to the forum!

Put this code in a Module.

Sub Copy3Sheets()
  Dim ws(1 To 3) As Worksheet, v As Variant, s As String
  
  Set ws(1) = Worksheets("Yearly Lease Order")
  Set ws(2) = Worksheets("Yearly Quote Form")
  Set ws(3) = Worksheets("Yearly Invoice Form")
  
  For Each v In ws()
    s = Worksheets("Costings Main Menu").Range("C4").Value2
    s = s & " " & v.Name
    If Not WorkSheetExists(s) Then
      v.Copy after:=Worksheets(Worksheets.Count)
      ActiveSheet.Name = s
    End If
  Next v
   
   Worksheets("Costings Main Menu").Activate
   Range("A1").Select
End Sub


 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook)
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function

Call the sub Copy3Sheets from an activex commandbutton's Click event:

Private Sub CommandButton1_Click()
  Copy3Sheets
End Sub

Thank you so much for the quick reply. Your code worked perfectly!!!!

rikk

Which poster are you refering to?

Sorry. Kenneth Hobson

Maybe:

Sub rikk1965()
Dim ws As Worksheet
Dim y As String
Set ws = ActiveSheet
Sheets("SIMAT Yearly Lease Order").Activate
y = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
ActiveSheet.Name = y & " " & ws.Range("C4").Value
Sheets("SIMAT Yearly Quote Form").Activate
y = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
ActiveSheet.Name = y & " " & ws.Range("C4").Value
Sheets("SIMAT Yearly Invoice Form").Activate
y = ActiveSheet.Name
ActiveSheet.Copy Before:=Sheets(1)
ActiveSheet.Name = y & " " & ws.Range("C4").Value
ws.Activate
End Sub