Hello all,
What I am trying to do is automate a series of select copy and select paste satements based off a cell value. The cell value that I am refrencing is months(Jan, Feb, Mar,etc...) I would like to have a button that when pressed looks at the cell B2's value then looks at the case for the appropriate value and performs the corresponding select, paste routines. I am not sure if the procedure I am using to perform this routine is the best method. Any help that any one could provide would be greatly apprciated. Thanks in advance

Bob

I won't post all the code because it is repeated for each month I will just include Jan through Mar. That way I won't exceed the character limit.

Option Explicit

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' This routine was written by Bob Kunkle 8/28/08 for the Windsor Plant                                             '
' What is being accomplished is an automation of copy paste for months end to the apporpriate department sheets    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub copy_paste()
Dim target As Range

If Not Intersect(target, Range("B2")) Is Nothing Then
Application.EnableEvents = False
Select Case LCase(target.Value)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Jan Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Jan"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("b2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("b2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("b2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("b2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("b2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("b2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("b2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("b2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("b2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("b2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("b2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("b2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("b2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Jan logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Feb Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Feb"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("c2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("c2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("c2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("c2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("c2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("c2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("c2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("c2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("c2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("c2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("c2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("c2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("c2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Feb logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' Start Mar Logic                                                                                                  '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Case "Mar"
   Sheet5.Range("b8:b22").copy
   Sheet13.Select
   Sheet13.Range("d2").Select
   Sheet13.Paste
   '
   Sheet5.Range("c8:c22").copy
   Sheet14.Select
   Sheet14.Range("d2").Select
   Sheet14.Paste
   '
   Sheet5.Range("d8:d22").copy
   Sheet15.Select
   Sheet15.Range("d2").Select
   Sheet15.Paste
   '
   Sheet5.Range("e8:e22").copy
   Sheet16.Select
   Sheet16.Range("d2").Select
   Sheet16.Paste
   '
   Sheet5.Range("f8:f22").copy
   Sheet17.Select
   Sheet17.Range("d2").Select
   Sheet17.Paste
   '
   Sheet5.Range("g8:g22").copy
   Sheet18.Select
   Sheet18.Range("d2").Select
   Sheet18.Paste
   '
   Sheet5.Range("h8:h22").copy
   Sheet19.Select
   Sheet19.Range("d2").Select
   Sheet19.Paste
   '
   Sheet5.Range("i8:i22").copy
   Sheet20.Select
   Sheet20.Range("d2").Select
   Sheet20.Paste
   '
   Sheet5.Range("j8:j22").copy
   Sheet21.Select
   Sheet21.Range("d2").Select
   Sheet21.Paste
   '
   Sheet5.Range("k8:k22").copy
   Sheet22.Select
   Sheet22.Range("d2").Select
   Sheet22.Paste
   '
   Sheet5.Range("l8:l22").copy
   Sheet23.Select
   Sheet23.Range("d2").Select
   Sheet23.Paste
   '
   Sheet5.Range("m8:m22").copy
   Sheet24.Select
   Sheet24.Range("d2").Select
   Sheet24.Paste
   '
   Sheet5.Range("o8:o22").copy
   Sheet27.Select
   Sheet27.Range("d2").Select
   Sheet27.Paste
  
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                                                                                                  '
' End Mar logic                                                                                                    '
'                                                                                                                  '
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    
End Select
End If
Application.EnableEvents = True

End Sub