Copy From Master to Different Sheets

Closed
toneloke - Mar 16, 2012 at 04:53 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 24, 2012 at 09:15 AM
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 June 14, 2009 Status Contributor Last seen August 7, 2021 811
Mar 16, 2012 at 07:13 AM
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 January 28, 2010 Status Contributor Last seen May 5, 2022 766
Mar 24, 2012 at 09:15 AM
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