Copy From Master to Different Sheets

Closed
toneloke - 16 Mar 2012 à 04:53
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 24 Mar 2012 à 09:15
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

Related:

2 responses

venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
16 Mar 2012 à 07:13
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.
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
24 Mar 2012 à 09:15
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