Code to long - needs help on "if" statements

J

JTWarthogs

Hello,
Can someone help me - here is the code that I have but it is to long. I haev
attached the first part - as you can see it goes for date=31 days. Its the
if-then statements that are to long (i haev the first 3 dates below). Can
someone help make a shorter if statement that will scroll throught the dates
and change the location on the ranges? Thanks

Private Sub CommandButton1_Click()
Worksheets("73").Activate
Set dateCurrent = ActiveSheet.Cells(38, 1)
Worksheets("73-1 Data").Activate
Set date1 = ActiveSheet.Cells(14, 1)
Set date2 = ActiveSheet.Cells(15, 1)
Set date3 = ActiveSheet.Cells(16, 1)
Set date4 = ActiveSheet.Cells(17, 1)
Set date5 = ActiveSheet.Cells(18, 1)
Set date6 = ActiveSheet.Cells(19, 1)
Set date7 = ActiveSheet.Cells(20, 1)
Set date8 = ActiveSheet.Cells(21, 1)
Set date9 = ActiveSheet.Cells(22, 1)
Set date10 = ActiveSheet.Cells(23, 1)
Set date11 = ActiveSheet.Cells(24, 1)
Set date12 = ActiveSheet.Cells(25, 1)
Set date13 = ActiveSheet.Cells(26, 1)
Set date14 = ActiveSheet.Cells(27, 1)
Set date15 = ActiveSheet.Cells(28, 1)
Set date16 = ActiveSheet.Cells(29, 1)
Set date17 = ActiveSheet.Cells(30, 1)
Set date18 = ActiveSheet.Cells(31, 1)
Set date19 = ActiveSheet.Cells(32, 1)
Set date20 = ActiveSheet.Cells(33, 1)
Set date21 = ActiveSheet.Cells(34, 1)
Set date22 = ActiveSheet.Cells(35, 1)
Set date23 = ActiveSheet.Cells(36, 1)
Set date24 = ActiveSheet.Cells(37, 1)
Set date25 = ActiveSheet.Cells(38, 1)
Set date26 = ActiveSheet.Cells(39, 1)
Set date27 = ActiveSheet.Cells(40, 1)
Set date28 = ActiveSheet.Cells(41, 1)
Set date29 = ActiveSheet.Cells(42, 1)
Set date30 = ActiveSheet.Cells(43, 1)
Set date31 = ActiveSheet.Cells(44, 1)
Let Worksheets("73").Range("b42") = Worksheets("73").Range("j12")
Let Worksheets("73").Range("b56") = Worksheets("73").Range("u14")
Let Worksheets("73").Range("b70") = Worksheets("73").Range("j33")
Let Worksheets("73").Range("b84") = Worksheets("73").Range("u28")
If dateCurrent = date1 Then
Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1
Data").Range("b14")
Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1
Data").Range("c14")
Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1
Data").Range("d14")
Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1
Data").Range("e14")
Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1
Data").Range("f14")
Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2
Data").Range("b14")
Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2
Data").Range("c14")
Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2
Data").Range("d14")
Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2
Data").Range("e14")
Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2
Data").Range("f14")
Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3
Data").Range("b14")
Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3
Data").Range("c14")
Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3
Data").Range("d14")
Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3
Data").Range("e14")
Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3
Data").Range("f14")
Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4
Data").Range("b14")
Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4
Data").Range("c14")
Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4
Data").Range("d14")
Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4
Data").Range("e14")
Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4
Data").Range("f14")

ElseIf dateCurrent = date2 Then
Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1
Data").Range("b15")
Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1
Data").Range("c15")
Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1
Data").Range("d15")
Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1
Data").Range("e15")
Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1
Data").Range("f15")
Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2
Data").Range("b15")
Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2
Data").Range("c15")
Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2
Data").Range("d15")
Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2
Data").Range("e15")
Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2
Data").Range("f15")
Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3
Data").Range("b15")
Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3
Data").Range("c15")
Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3
Data").Range("d15")
Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3
Data").Range("e15")
Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3
Data").Range("f15")
Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4
Data").Range("b15")
Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4
Data").Range("c15")
Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4
Data").Range("d15")
Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4
Data").Range("e15")
Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4
Data").Range("f15")

ElseIf dateCurrent = date3 Then
Worksheets("73").Range("b40").Copy Destination:=Worksheets("73-1
Data").Range("b16")
Worksheets("73").Range("b41").Copy Destination:=Worksheets("73-1
Data").Range("c16")
Worksheets("73").Range("b42").Copy Destination:=Worksheets("73-1
Data").Range("d16")
Worksheets("73").Range("b43").Copy Destination:=Worksheets("73-1
Data").Range("e16")
Worksheets("73").Range("b44").Copy Destination:=Worksheets("73-1
Data").Range("f16")
Worksheets("73").Range("b54").Copy Destination:=Worksheets("73-2
Data").Range("b16")
Worksheets("73").Range("b55").Copy Destination:=Worksheets("73-2
Data").Range("c16")
Worksheets("73").Range("b56").Copy Destination:=Worksheets("73-2
Data").Range("d16")
Worksheets("73").Range("b57").Copy Destination:=Worksheets("73-2
Data").Range("e16")
Worksheets("73").Range("b58").Copy Destination:=Worksheets("73-2
Data").Range("f16")
Worksheets("73").Range("b68").Copy Destination:=Worksheets("73-3
Data").Range("b16")
Worksheets("73").Range("b69").Copy Destination:=Worksheets("73-3
Data").Range("c16")
Worksheets("73").Range("b70").Copy Destination:=Worksheets("73-3
Data").Range("d16")
Worksheets("73").Range("b71").Copy Destination:=Worksheets("73-3
Data").Range("e16")
Worksheets("73").Range("b72").Copy Destination:=Worksheets("73-3
Data").Range("f16")
Worksheets("73").Range("b82").Copy Destination:=Worksheets("73-4
Data").Range("b16")
Worksheets("73").Range("b83").Copy Destination:=Worksheets("73-4
Data").Range("c16")
Worksheets("73").Range("b84").Copy Destination:=Worksheets("73-4
Data").Range("d16")
Worksheets("73").Range("b85").Copy Destination:=Worksheets("73-4
Data").Range("e16")
Worksheets("73").Range("b86").Copy Destination:=Worksheets("73-4
Data").Range("f16")

Else: ActiveSheet.Cells(51, 1) = "no"
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Column and row advance 1
Macro to Copy/Paste Multiple images 3
Creating Pivot Table with Macro using Multiple Sheets 0
help needed! how to clear my macro tabulate table 2
Another 'Copy To The Next Available Row' Question 2
Paste Link to data Help!!! 0
first sip of working with range in vba excel 6
Insert value in text box based on previous tselected value. 2

Top