I am trying to create a Vb script in Excel that will take 6 textbox inputs and place them in the desired locations (i have that working perfectly) next i need it to create "X" number of copies and increment a specific Cell. I have this working to a point. I get two sheets that have "1 of X" the rest of the copied sheets are fine... "2 of X".... "3 of X" and so on. When i have more then around 10 sheets created Excel Freezes when i try to print or delete all the Sheets (except Sheet1). Any thoughts on what i am doing wrong? Thanks for any input.
Private Sub CommandButton1_Click()
' sets my text from the text boxes to the excel cells
Range("C3").Value = TextBox2.Text
Range("C4").Value = TextBox3.Text
Range("C5").Value = TextBox4.Text
Range("C6").Value = TextBox5.Text
Range("E11").Value = TextBox6.Text
Range("I15").Value = TextBox1.Text
Dim p As Integer
' this is my value that gets incremented so its started at 1
Range("G15").Value = "1"
' this is my loop that creates each sheet and increments the X of X number
For p = 0 To (TextBox1.Text - 1)
Sheet1.Copy After:=Sheet1
Range("G15").Value = (p + 1)
Range("C3").Value = TextBox2.Text
Range("C4").Value = TextBox3.Text
Range("C5").Value = TextBox4.Text
Range("C6").Value = TextBox5.Text
Range("E11").Value = TextBox6.Text
Range("I15").Value = TextBox1.Text
Next p
End Sub
Private Sub CommandButton2_Click()
'This deletes all my extra sheets and clears my text boxes
Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then ws.Delete
Next
Application.DisplayAlerts = True
Range("C3").Value = ""
Range("C4").Value = ""
Range("C5").Value = ""
Range("C6").Value = ""
Range("E11").Value = ""
Range("I15").Value = ""
Range("G15").Value = ""
End Sub
Private Sub CommandButton3_Click()
'My button on the excel sheet to open the user form
UserForm1.Hide
End Sub
Private Sub CommandButton4_Click()
'This is my print out the sheets button
Dim ws As Worksheet
Dim i As Integer
i = 0
For Each ws In ActiveWorkbook.Worksheets
If (i = 0) Then
ws.Select
Else
ws.Select False
End If
i = i + 1
Next ws
Application.Dialogs(xlDialogPrinterSetup).Show
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
Update (may help to have the error information) I can confirm it happens on anything over 9 for the TextBox1.Text
Error says.....
Run-time error '-2147417848(80010108)':
Automation error The object invoked has disconnected from its clients
Change your
Sheet1.Copy After:=Sheet1
to
Sheet1.Copy Before:=Sheet1
The error maybe related to the application freezing from running the macro. Try disabling the screen update while your actions are running.
Application.ScreenUpdating = False
Application.ScreenUpdating = True
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments