Excel VBA same file copy sheet1 to after last sheet with variable name 1

[03Explorer]

I found a solution to do this but with Excel 365 it is not allowing it. I am building a Sub to do this... Code below:

Sub PopulateExcel(strName, strNameID, strPractice, strRole, strServiceType)
              Dim MyXL As Object
              Dim MySheetName As String
              
        'copy Sheet1
              Select Case strRole
            Case "CRM"
                Debug.Print "CRM is the primary value."
            'open Excel file as defined in CompiledDirectory
              Set MyXL = CreateObject("Excel.Application")
              MySheetName = strNameID & "-" & strName Sheets("Sheet1").Copy After:=wb.Worksheets(wb.Worksheets.Count)              'compile new sheet name
                Debug.Print "New Tab Name = '& MySheetName & " '"
              
              ActiveSheet.Name = MySheetName                        'activate new sheet

I am stuck with MySheetName = . VBA is not allowing me to use an example I found.

Sub CopySheet()
Dim MySheetName As String 
MySheetName = "TestSheet" Sheets("MasterSheet").Copy After:=Sheets("MasterSheet") 
ActiveSheet.Name = MySheetName
End Sub

 

[SkipVought]

Sub CopySheet()
Sheets("MasterSheet").Copy After:=Sheets("MasterSheet") 
ActiveSheet.Name = “TestSheet”
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Skip, I have a hidden agenda that I didn't explain previously is to have this be a sub so it can insert a new sheet at the end of all sheets... so for the example you give, it is using the known sheet names...

For the first go around this would work, but after Nth time it wouldn't.

Looking for something that does this

Sub PopultateExcel(Name, ID)
VariableName = ID & "-" & Name
Sheets(1).Copy After:=wb.Worksheets(wb.Worksheets.Count)
ActiveSheet.Name = VariableName

Would this work or do I need to define variables (IE wb, VariableName) within the Sub?

Thanks!
Rob

 

[Andrzejek]

sub so it can insert a new sheet at the end of all sheets... "

Try this code:

Option Explicit
Dim i As Integer

Sub AddSheets()

i = i + 1
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "NewSheet" & i

End Sub

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Sure, but part of what I was illustrating was there’s no need for the variable if the name is assigned one time...

Sub PopultateExcel(Name, ID)
   Sheets(1).Copy After:=wb.Worksheets(wb.Worksheets.Count)
   ActiveSheet.Name = ID & "-" & Name
‘...
End Sub

...and, yes, this will copy the sheet to the last position in the workbook. Or you could do [tt]Sheets(1).Move [/tt]to move the sheet to the last position in the workbook.

Using a Called procedure, you still need to supply a unique name, in your case via two variables; Name & ID. But you do not necessarily need to assign them to another variable within your precedure.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Thank you Andy! I tried your suggestion and it didn't work for what I am doing.

Skip, I believe your suggestion is going to be my solution. However, I have a question... Do I need to declare 'wb.' in the sub or the main function? What I may need to explain is that I am creating this script in MS Access to populate Excel files using the database.

Thanks,
Rob

 

[SkipVought]

If you reference multiple workbooks, this varisble must be passed to this procedure. Otherwise no workbook reference is required.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Okay, so my question is this... I am now in a sub function... How do I tell the sub that I want to focus on the excel sheet I opened in the main function and make that my focused excel object? Do I need to declare the excel object again (in the sub)? thinking yes. Also, I am now passing the workbook name with the sub call. I am kind of at a loss at this point.

Thanks Skip!

 

[SkipVought]

We need some clarification, because when you ask a focused wuestion, such as the original question, you get a focused answer.

It seemed that you have a procedure, PopultateExcel, that has two arguments and now you want to include references to other worksheets or workbooks.

Well that raises a question in my mind. What’s the calling procedure doing and what is the purpose of the PopultateExcel procedure, a separate called procedure?

Does PopultateExcel get called by other procedures or at other places in the current calling procedure?

Please answer each of these questions concisely, clearly and completely.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Thanks Skip, I appreciate your response. I was trying to get something but without sharing or letting you know it was coming from MS Access. I realized the response given was if I was IN excel and working WITHIN excel. My Project: In Access, I have a person identifying a team of people for a project. When the form is populated with people, it then uses logic to gather information on these people (from tables) in order to build a spreadsheet(Workbook) for each team member. The spreadsheet is a template file that will be copied. There can be up to 12 files total, but the catch is each file will be populated with questions pertaining to each populated team member. What I've asked here, is the piece after files are built. I have a loop that is populating the excel workbook with the questions that pertain to each team member. Also, the team members have their own tab in this workbook. The purpose of this project is a survey building environment for project teams to rate each other on specific tasks tied to their skill, title, and role.

What’s the calling procedure doing and what is the purpose of the PopultateExcel procedure, a separate called procedure?
I am trying to create a procedure/sub that minimizes the VBA code repetition for the tab building experience due to Roles dictating different situations. Currently, I have one procedure that'll call this new one I am working on with your aid.

Does PopultateExcel get called by other procedures or at other places in the current calling procedure?
It get's called by the primary procedure/function I've built and am working to keep consolidated vs having it be too long. This procedure/sub is called within a loop. So it could be called upon 12 times.

I hope this helps you understand. My apologies, I asked a question that didn't involve the fact that it's coming from MS Access.

 

[SkipVought]

Apparently you are coding your VBA in an application other than Excel.

Therefore, you should have coded to use either CreatObject or GetObject to instantiate an Excel Application Object or you have a direct reference to an Excel Object Library.

With those things in mind, if you have Set an Excel object such a Workbook, Worksheet or Range, for instance, and you intend to call a procedure, then ALL the necessary Excel Object relevant references must be passed to the procedure as arguments unless these Excel Object references have been declared as Public variables appropriately.

Assuming the former, then...

Sub PopultateExcel(Name, ID, wb)
   wb.Sheets(1).Copy After:=wb.Worksheets(wb.Worksheets.Count)
   wb.Worksheets(wb.Worksheets.Count).Name = ID & "-" & Name
‘...
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[03Explorer]

Skip, thank you! I am breaking through my mental block of I know I can do XYZ but how to code for it is now making movement again!!

*I will try and come back to say how it works, I am hit or miss on responding with the outcome (My apologies if I do)*

*I gave your post a "Great POST!"

Rob