Can this VBA be simplified

**Toonies** · 12-26-2012, 03:41 AM

Hi and thanks for looking at my thread.

I have the following VBA and it changes the dates on different sheets when the date is changed on the "DATE" sheet

the VBA does the job just wondering if it can be simplified.
heres the code

Sub Macro4()
'
' Macro4 Macro
'

'
Call UnprotectSh
Application.EnableEvents = False
Sheets("Sheet2").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[2]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[3]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[4]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[5]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[6]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[7]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[8]C[-29]"
    Range("A2").Select
Sheets("Sheet3").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[9]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[10]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[11]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[12]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[13]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[14]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[15]C[-29]"
    Range("A2").Select
Sheets("Sheet4").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[16]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[17]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[18]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[19]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[20]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[21]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[22]C[-29]"
    Range("A2").Select
Sheets("Sheet5").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[23]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[24]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[25]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[26]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[27]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[28]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[29]C[-29]"
    Range("A2").Select
Sheets("Sheet6").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[30]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[31]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[32]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[33]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[34]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[35]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[36]C[-29]"
    Range("A2").Select
Sheets("Sheet7").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[37]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[38]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[39]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[40]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[41]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[42]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[43]C[-29]"
    Range("A2").Select
Sheets("Sheet8").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[44]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[45]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[46]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[47]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[48]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[49]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[50]C[-29]"
    Range("A2").Select
Sheets("Sheet9").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[51]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[52]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[53]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[54]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[55]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[56]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[57]C[-29]"
    Range("A2").Select
Sheets("Sheet10").Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[58]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[59]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[60]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[61]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[62]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[63]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[64]C[-29]"
    Range("A2").Select
Application.EnableEvents = True
Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")

            
Sheet2.Select
Range("AL1").Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Select
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

many thanks

Toonies

**patel45** · 12-26-2012, 03:51 AM

Sub Macro4()
Call UnprotectSh
Application.EnableEvents = False
for sh=2 to 10
    Sheets(sh).Select
    Range("C1:F1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[2]C[1]"
    Range("H1:K1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[3]C[-4]"
    Range("M1:P1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[4]C[-9]"
    Range("R1:U1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[5]C[-14]"
    Range("W1:Z1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[6]C[-19]"
    Range("AB1:AE1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[7]C[-24]"
    Range("AG1:AJ1").Select
    ActiveCell.FormulaR1C1 = "=DATE!R[8]C[-29]"
    Range("A2").Select
next
Application.EnableEvents = True
Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")
           
Sheet2.Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

**Toonies** · 12-26-2012, 04:07 AM

Hi thanks for your suggestion, though it throughs up errors

Run-time error '1004':

Select method of Worksheet class failed

this is the problem line

Sheets(sh).Select

also the date should change by 1 in each cell within the range on each sheet

ie: each sheet is 7 days ahead of the previous sheet

**Toonies** · 12-26-2012, 07:02 AM

I'm open to any further ideas

**mike7952** · 12-26-2012, 07:54 AM

Maybe this

Sub Macro4()
Dim shArr

Call UnprotectSh

shArr = Array("Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10")
Application.EnableEvents = False
 For shname = 0 To UBound(shArr)
    With Worksheets(shArr(shname))
        .Range("C1").FormulaR1C1 = "=DATE!R[2]C[1]"
        .Range("H1").FormulaR1C1 = "=DATE!R[3]C[-4]"
        .Range("M1").FormulaR1C1 = "=DATE!R[4]C[-9]"
        .Range("R1").FormulaR1C1 = "=DATE!R[5]C[-14]"
        .Range("W1").FormulaR1C1 = "=DATE!R[6]C[-19]"
        .Range("AB1").FormulaR1C1 = "=DATE!R[7]C[-24]"
        .Range("AG1").FormulaR1C1 = "=DATE!R[8]C[-29]"
    End With
 Next
Application.EnableEvents = True
Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")

Sheet2.Select
Range("AL1").Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Select
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

**AB33** · 12-26-2012, 08:08 AM

Untested

Sub Macro4()
'
' Macro4 Macro

Call UnprotectSh
Application.EnableEvents = False

With Sheets("Sheet2")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[2]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[3]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[4]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[5]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[6]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[7]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[8]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet3")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[9]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[10]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[11]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[12]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[13]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[14]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[15]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet4")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[16]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[17]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[18]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[19]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[20]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[21]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[22]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet5")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[23]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[24]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[25]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[26]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[27]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[28]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[29]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet6")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[30]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[31]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[32]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[33]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[34]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[35]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[36]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet7")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[37]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[38]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[39]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[40]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[41]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[42]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[43]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet8")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[44]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[45]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[46]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[47]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[48]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[49]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[50]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet9")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[51]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[52]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[53]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[54]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[55]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[56]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[57]C[-29]"
    .Range("A2").Select
    End With
With Sheets("Sheet10")
    .Range("C1:F1").FormulaR1C1 = "=DATE!R[58]C[1]"
    .Range("H1:K1").FormulaR1C1 = "=DATE!R[59]C[-4]"
    .Range("M1:P1").FormulaR1C1 = "=DATE!R[60]C[-9]"
    .Range("R1:U1").FormulaR1C1 = "=DATE!R[61]C[-14]"
    .Range("W1:Z1").FormulaR1C1 = "=DATE!R[62]C[-19]"
    .Range("AB1:AE1").FormulaR1C1 = "=DATE!R[63]C[-24]"
    .Range("AG1:AJ1").FormulaR1C1 = "=DATE!R[64]C[-29]"
    .Range("A2").Select
    End With
Application.EnableEvents = True
Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")

            
With Sheet2.Range("AL1")
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Range("AL1").ActiveCell.FormulaR1C1 = ""
.Range("AL1").Select
End With
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

**Toonies** · 12-26-2012, 08:43 AM

here is a copy of the spreadsheet

Demo 1a.xls

**Toonies** · 12-26-2012, 08:37 AM

Hi AB33 your code is like my original code that I am looking to reduce.

mike7592 I amended your code to the following

Sub Macro4()
Dim shArr

Call UnprotectSh

Sheets(Array(10, 9, 8, 7, 6, 5, 4, 3)).Select
Application.EnableEvents = False
 For shname = 2 To 10
    With Worksheets(shname)
        .Range("C1").FormulaR1C1 = "=DATE!R[2]C[1]"
        .Range("H1").FormulaR1C1 = "=DATE!R[3]C[-4]"
        .Range("M1").FormulaR1C1 = "=DATE!R[4]C[-9]"
        .Range("R1").FormulaR1C1 = "=DATE!R[5]C[-14]"
        .Range("W1").FormulaR1C1 = "=DATE!R[6]C[-19]"
        .Range("AB1").FormulaR1C1 = "=DATE!R[7]C[-24]"
        .Range("AG1").FormulaR1C1 = "=DATE!R[8]C[-29]"
    End With
 Next
Application.EnableEvents = True
Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")

Sheet2.Select
Range("AL1").Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Select
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

also the date should change by 1 in each cell within the range on each sheet (ie: each sheet starts 7 days ahead of the previous sheet)

but however with the amended code it is putting the same 7 dates on each sheet.

**mike7952** · 12-26-2012, 08:43 AM

With your amending theres no reason to loop thru 2 to 10. Your selecting all the sheets already.

Sub Macro4()
Dim shArr

Call UnprotectSh

Sheets(Array(10, 9, 8, 7, 6, 5, 4, 3)).Select
Application.EnableEvents = False

Range("C1").FormulaR1C1 = "=DATE!R[2]C[1]"
Range("H1").FormulaR1C1 = "=DATE!R[3]C[-4]"
Range("M1").FormulaR1C1 = "=DATE!R[4]C[-9]"
Range("R1").FormulaR1C1 = "=DATE!R[5]C[-14]"
Range("W1").FormulaR1C1 = "=DATE!R[6]C[-19]"
Range("AB1").FormulaR1C1 = "=DATE!R[7]C[-24]"
Range("AG1").FormulaR1C1 = "=DATE!R[8]C[-29]"

Application.EnableEvents = True

Sheets("DATE").Range("B2").FormulaR1C1 = Format(Now(), "mm/dd/yyyy")

Sheet2.Select
Range("AL1").Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Select
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select
 
 Call UnHideSheets
 Call ProtectSh
 Call PickDate
End Sub

**Andrew-R** · 12-26-2012, 08:44 AM

You can simplify this section:

With Worksheets(shname)
        .Range("C1").FormulaR1C1 = "=DATE!R[2]C[1]"
        .Range("H1").FormulaR1C1 = "=DATE!R[3]C[-4]"
        .Range("M1").FormulaR1C1 = "=DATE!R[4]C[-9]"
        .Range("R1").FormulaR1C1 = "=DATE!R[5]C[-14]"
        .Range("W1").FormulaR1C1 = "=DATE!R[6]C[-19]"
        .Range("AB1").FormulaR1C1 = "=DATE!R[7]C[-24]"
        .Range("AG1").FormulaR1C1 = "=DATE!R[8]C[-29]"
    End With

To:

With Worksheets(shname)
  For lLoop=0 To 6
        .Cells(1,3+lLoop*5).FormulaR1C1 = "=DATE!R[" & lLoop+2 & "]C[" & 1-lLoop*5 * "]"
  Next lLoop
End With

**Andrew-R** · 12-26-2012, 08:46 AM

And this section:

Sheet2.Select
Range("AL1").Select
MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
Sheet2.Select
Range("AL1").Select
ActiveCell.FormulaR1C1 = ""
Range("AL1").Select

Can be reduced to:

MsgBox "Hi, Your Weekly Timesheets have been added simply amend your start date"
With Sheet2.Range("AL1")
  ActiveCell.FormulaR1C1 = ""
  .Select
End With

**Toonies** · 12-26-2012, 09:01 AM

Hi Andrew-R

I get a Run Time error '13'

and this part of the code is highlighted

.Cells(1, 3 + lLoop * 5).FormulaR1C1 = "=DATE!R[" & lLoop + 2 & "]C[" & 1 - lLoop * 5 * "]"

**Andrew-R** · 12-26-2012, 09:01 AM

Sorry, the final "*" should be an "&"

**Toonies** · 12-26-2012, 09:20 AM

Ok now I have got that to work but it still populates each sheet with the same 7 dates

in my original code it populated each sheet with a with 7 dates each sheet dates 1 week ahead of the previous sheets starting date

hope I'm explaining myself correctly

**Toonies** · 12-26-2012, 04:50 PM

Hi I'm still open to ideas

**watersev** · 12-26-2012, 06:09 PM

hi there, the code inbetween the following two lines:

Application.EnableEvents = False
......
Application.EnableEvents = True

can be replaced with the following:

ReDim arr(1 To 1, 1 To 34)

rcounter = 1

For i = Sheets("Sheet2").Index To Sheets.Count
    ccounter = 6
    For n = 1 To 34 Step 5
        rcounter = rcounter + 1
        ccounter = ccounter - 5
        arr(1, n) = "=DATE!R[" & rcounter & "]C[" & ccounter & "]"
    Next
    Sheets(i).Range("c1:aj1") = arr
Next

**Toonies** · 12-27-2012, 01:34 AM

Watersev now thats a cool bit of coding many thanks it works a treat

could you explain how it actually works

thanks

Toonies

**watersev** · 12-27-2012, 05:49 AM

loop from Sheet "Sheet2" till sheets count, for each sheet an array is filled with formulas and pasted to the sheet

**Toonies** · 12-27-2012, 02:10 PM

Many thanks

Can this VBA be simplified

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Re: Can this VBA be simplified

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions