Code Assistance

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
I have code that I need help with if anyone can assist. Code as follows:

Private Sub CreateClass_Click()
Dim ClassSheet As Worksheet
For Each ClassSheet In Worksheets
Range("FORMULACOPY").Copy
ClassSheet.Select
Range("E5").pastespecial Paste:=7, Operation:=xlNone, SkipBlanks:=False, _
Transpose:=False
Next ClassSheet
frmChoice.Hide 'Unload Me
End Sub

This is a button on a form. What I am trying to do is Copy the range on visible Worksheet names that start with 0. How can I skip certain worksheets?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
after the For Each ...

i.e your sheet is selected

Have a definition that refers to the sheet

ie.

LCSHEET = Left(ClassSheet,1)

If LCSHEET <> 0 Then GoTo 99

99 should be entered as the last bit of code before the NEXT CLASSSHEET (so it skips the remainder of the macro in the loop and just loops to the next ClassSheet)

LCSHEET simply checks the first character of ClassSheet

Any help?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
Private Sub CreateClass_Click() 
   Dim ClassSheet As Worksheet 
   Range("FORMULACOPY").Copy 
   For Each ClassSheet In Worksheets 
      If ClassSheet.Visible = True Then
         If Left(ClassSheet.Name,1) = "0" Then
            ClassSheet.Range("E5").PasteSpecial Paste:=7, Operation:=xlNone, SkipBlanks:=False, _ 
Transpose:=False 
         End If
      End If
   Next ClassSheet 
frmChoice.Hide 'Unload Me 
End Sub
 

Aaron

Board Regular
Joined
Feb 20, 2002
Messages
237
Thank you both either one works great. Now here is a second question. My form lets the user select (via checkbox) a class. There are 50 classes to choose from, and each class has its own worksheet. The formulacopy range is on its own worksheet sheet as well. What would either of you recommend to clear the formulas if the checkbox is deselected? BTW thanks for your quick reply.
 

Forum statistics

Threads
1,144,220
Messages
5,723,091
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top