×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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:

CODE

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.

CODE

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

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

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

CODE

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

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

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

Try this code:

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.

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

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

CODE

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 Sheets(1).Move 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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

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

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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!

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

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

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...

CODE

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close