Help with nested VBA If statement statements, another, correct, getting

andy

Hello Board,

I am trying to write an if statement that checks cell B1 on the constraints worksheet for a value, and when that value is met, copy from another worksheet, and paste to the "Plan" worksheet within a given range. I keep getting an out of range condition. Does my if statements look correct. thanks. please see code below.

bboldt3

Sub Copyrange()
   If ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A10M0" Then       Worksheets("A10M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A10M10" Then       Worksheets("A10M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A10M21" Then       Worksheets("A10M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A1M0" Then       Worksheets("A1M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A1M10" Then       Worksheets("A1M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "A1M21" Then       Worksheets("A1M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B10M0" Then       Worksheets("B10M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B10M10" Then       Worksheets("B10M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B10M21" Then       Worksheets("B10M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B1M0" Then       Worksheets("B1M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B1M10" Then       Worksheets("B1M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "B1M21" Then       Worksheets("B1M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C10M0" Then       Worksheets("C10M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C10M10" Then       Worksheets("C10M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C10M21" Then       Worksheets("C10M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C1M0" Then       Worksheets("C1M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C1M10" Then       Worksheets("C1M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   ElseIf ThisWorkbook.Sheets("Constraints").Range("b1").Value = "C1M21" Then       Worksheets("C1M21").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   Else: ThisWorkbook.Sheets("G10M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")      End If         

End Sub

Not entirely sure I follow but perhaps a Select Case may be a little easier in this instance ?Code:
Sub Copyrange()
Select Case ThisWorkbook.Sheets("Constraints").Range("B1").Value   Case "A10M0", "A10M10", "A10M21", "A1M0", "A1M10", "A1M21", "B10M0", "B10M10", "B10M21", "B1M0", "B1M10", "B1M21", _"C10M0", "C10M21", "C1M0", "C1M0", "C1M10", "C1M21"       ThisWorkbook.Worksheets("A10M0").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")   Case Else       ThisWorkbook.Worksheets("G10M10").Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:hd682")
End Select
End Sub
If you get a Subscript out of Range error then this is because you do not have 1 or more of the following sheets in the workbook in which the VBA is stored:  Constraints, A10M0, G10M10, Plan

I'm sure a Select Case is a good idea, but couldn't this be done in a couple of lines.

Code:
strWsName = ThisWorkbook.Sheets("Constraints").Range("B1").Value
Worksheets(strWsName).Range("A2:AY38").Copy Worksheets("Plan").Range("FF646:HD682")Good spot Norie... I missed the differing sheet names amidst the ElseIfs

Pure genius norie.

Thanks a million. The State of Wisconsin DOA thanks you.

Ben