MACRO that allows user to copy and insert multiple worksheets through userform comman

Brand_New2VBA · Apr 23rd 2012

Greetings all VBA Gurus,

My UserForm has a ComboBox that allows users to select the number of forms they would like to add to the current work sheet. Once the numbers of forms are selected, through the ComboBox, I'd like the exact number listed in the ComboBox to be inserted after the third sheet in the workbook. However this code isn’t working and continues to give off errors. Can someone help be debug this problem? Where am I going wrong with this code? Is it possible to insert multiple sheets at a time? Microsoft certainly thinks so however the code continues to elude me.

Your advice and opinions are greatly needed. Thanks in advance guys.

So far this is what I have:

Code

Private Sub CommandButton1_Click()
    Dim x As Integer
    
    x = ComboBox.Value
    For numtimes = 1 To x
        
        ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")
    Next
 


End Sub

Display More

Sincerely,
Brand_New2VBA

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Change "combobox.value" to "combobox1.value" and it should work just fine.

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Thank you Sooper for your reply however that doesnt work either. I even attenpted to add an if statement within the code and that doesnt work this is what I have now:

Code

Private Sub CommandButton6_Click()
If OptionButton1.Value = True Then
Dim x As Integer
    
    x = ComboBox5.Value
    
    For numtimes = 1 To x
       
       ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")
        
    Next
End If
End Sub

Display More

the error keeps coming up here:

Code

ActiveWorkbook.Sheets("CC Request Form").Copy _
           After:=ActiveWorkbook.Sheets("Sheet7")

Do you have any other sugestions?

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

1. Try it without the "ActiveWorkbook." bit. When I put the code in, I left it out.
2. Make sure you have a tab called "CC REquest Form"

Can you post the file in question?

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Here's a file with the only change being the ComboBox1.value.

forum.ozgrid.com/index.php?attachment/45388/

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

I'm unable to see your code or run the macro on the sheet.

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

how exactly did you wrtie your code?

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

forum.ozgrid.com/index.php?attachment/45389/

Hi Sooper,

I've attached a sample file of my project. Please help me understand where I went wrong with the macro. Thank you!

Brand_New

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

1. In the file, the tab is called "CC Request Form 1" and the code is looking for "CC Request Form".

2. There is no "Sheet7" in the workbook.

Solution:

1. Change the code to

Code

ActiveWorkbook.Sheets("CC Request Form 1").Copy _ 
After:=ActiveWorkbook.Sheets("Sheet2")

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Hi Sooper,

The sheet ("CC Request Form") is hidden. Does that play a part in the error messages? Does it mater is the sheet being copied is hidden?

Brand_New2VBA · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Hi Sooper,

The sheet ("CC Request Form") is hidden. Does that play a part in the error messages? Does it mater is the sheet being copied is hidden?

this is what I have:

Code

Private Sub CommandButton6_Click()


Dim x As Integer
    
    x = ComboBox5.Value
    
    For numtimes = 1 To x
       
       ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")


        
    Next


End Sub

Display More

I wish I knew why it works for you and not for me. I still get errors here:

Code

ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Yes. You will have to unhide the sheet before copying, then rehiding it. However, there is another problem. The sheet name is actually "CC Request Form ". Please note carefully that you have a space after the word "Form" in the sheet name. That is a character, and so is not found by the code. Fix the sheet name, unhide the sheet prior to copying, then rehide when done. Like so:

Code

Sheets("CC Request Form").Visible = True

and

Code

Sheets("CC Request Form").Visible = False

Put these at the beginning and end of each of the three commanbutton click subs.

Sooper · Apr 23rd 2012

Re: MACRO that allows user to copy and insert multiple worksheets through userform co

Quote from Brand_New2VBA;605769
Hi Sooper,

The sheet ("CC Request Form") is hidden. Does that play a part in the error messages? Does it mater is the sheet being copied is hidden?

this is what I have:
Code
Private Sub CommandButton6_Click()


Dim x As Integer
    
    x = ComboBox5.Value
    
    For numtimes = 1 To x
       
       ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")


        
    Next


End Sub
Display More
I wish I knew why it works for you and not for me. I still get errors here:
Code
ActiveWorkbook.Sheets("CC Request Form 1").Copy _
After:=ActiveWorkbook.Sheets("Sheet2")
Display More

OK. I just saw this reply you made. I copied the code exactly and checked it and found that the tab name is "CC Request Form 1". It has two spaces between "Form" and "1". In the VBA code, there is only one space between "Form" and "1". You have to be extremely careful when referring to names with code that there aren't extra spaces, because they are hard to see. I recommend copying the tab name and pasting it in Word or in the VBA code itself to ensure an identical match. Does that help?

MACRO that allows user to copy and insert multiple worksheets through userform comman

Participate now!

Share