Copy From Master to Different Sheets

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am slowly making my way through macros, but can't seem to find the answer to the following:-

I have a master sheet - which is a budget. From this sheet I want to create various alternative sheets that are copies of the master and will fill different roles, but they will not follow a specific order in being processed. Is there a macro detail that sets it up so when you click the macro it creates the sheet on the next available sheet, rather than specifying sheet 1 or sheet 2 etc?

Thanks kindly
T

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
802
some more information is required. onfigsuration ofm master sheet. condition for part of the data to be distributed to other sheets and how are these named based on a cell name in the master sheet. post your master sheet and explain.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Seems to me that you want to create a sheet with a specific name. You can rename the sheet in the macro like
Sheet.add <name here>

You can also use the macro below which allows to add a sheet, and returns the newly added sheet name back to the calling point

' The function adds a new sheet
' if a sheet name is passed is parameter, it will attempt to name the sheet as that
'  it will return the newly added sheet name
' Sample Calls :   Call addSheet
'                  Call addSheet("A Test")
Public Function addSheet(Optional sheetName As String) As String

   Dim wsNew            As Worksheet
   Dim tmpWs            As Worksheet
   Dim newSheetName     As String
      
   Set tmpWs = ActiveSheet
   Set wsNew = Worksheets.Add
   On Error Resume Next
   Err.Clear
   Sheets(sheetName).Activate
   If (Err.Number <> 0) _
   Then
     wsNew.Name = sheetName
   End If
   addSheet = wsNew.Name
   tmpWs.Activate
   Set tmpWs = Nothing
   Set wsNew = Nothing
   Err.Clear
   On Error GoTo 0
      
End Function