Help with Else/If statements

[nikdwg7]

I'm new to writing macros and would like to check if a Sheet name contains a '#' or not.. ie: "1" vs. "1#". So far I have this:
If Sheets("1#") Then Sheets("Final Schedule").Select
Range("B4:Y4").Select
Selection.Copy
Sheets("1#").Select
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ElseIf Sheets("1") Then Sheets("Final Schedule").Select
Range("B4:G4").Select
Selection.Copy
Sheets("1").Select
Range("C14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Final Schedule").Select
Range("H4:W4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("1").Select
Range("I12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Final Schedule").Select
Range("X4:Y4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("1").Select
Range("Y14").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

But am getting the msg: Compile error: Else without If

I'm sure this is something simple, but I can't figure it out

 

[jproffer]

If Sheets("1#") Then
    Sheets("Final Schedule").Range("B4:Y4").Copy
    Sheets("1#").Range("C14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ElseIf Sheets("1") Then
    Sheets("Final Schedule").Range("B4:G4").Copy
    Sheets("1").Range("C14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Final Schedule").Range("H4:W4").Copy
    Sheets("1").Range("I12").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Final Schedule").Range("X4:Y4").Copy
    Sheets("1").Range("Y14").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode=False
End If

Try that. You don't have to do all the selecting and such. And the CutCopyMode=False is irrelevant because when you start to copy a new range, it knows you want to release the last range you copied.

Else without If was because you were finishing your IF statements on the same line as you setup the condition, if you want an "opposite operation" (else) to your if condition you have to put the true operation on a seperate line. If you only want one thing to happen like:

If Range("A1").value=1 then Range("B5").Value=10

you can do it on one line, and no End If is needed.

One more edit ...just a constructive criticism...learn to indent your code, it makes it much easier to read for you....ok and us too

 

[hiker95]

nikdwg7,

Welcome to the MrExcel board.


The code will first check to see if either sheets "1" or "1X" exist.

If they do exist the ranges in sheets "1" and "1X" will be replaced by the values from sheet "Final Schedule".

If they do not exist they will be created, and the ranges in sheets "1" and "1X" will be replaced by the values from sheet "Final Schedule".



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub Test()

Application.ScreenUpdating = False

On Error Resume Next
Sheets("1#").Select
If Err Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "1#"
On Error GoTo 0
Sheets("1#").Range("C14:Z14").Value = Sheets("Final Schedule").Range("B4:Y4").Value


On Error Resume Next
Sheets("1").Select
If Err Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "1"
On Error GoTo 0
Application.CutCopyMode = False
Sheets("1").Range("C14:H14").Value = Sheets("Final Schedule").Range("B4:G4").Value
Sheets("1").Range("I12:X12").Value = Sheets("Final Schedule").Range("H4:W4").Value
Sheets("1").Range("Y14:Z14").Value = Sheets("Final Schedule").Range("X4:Y4").Value

Application.ScreenUpdating = True

End Sub

Then run the "Test" macro.