Make Copy/Paste Values Macro more efficient when calculating

**Phily915** · 02-17-2014, 06:50 PM

I got a macro to copy and paste values onto another tab within my worksheet. I have a lot of data and currently takes about 30 seconds to calculate and paste. Not sure if its an issue with my macro or with my computer (Mac - Excel 2011).

Here is an example of my macro:

Sub SimulateWeek()

If Range("AdvanceWeek").Value = "Week 1" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2").PasteSpecial Paste:=xlPasteValues
ElseIf Range("AdvanceWeek").Value = "Week 2" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C515").PasteSpecial Paste:=xlPasteValues
ElseIf Range("AdvanceWeek").Value = "Week 3" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C1028").PasteSpecial Paste:=xlPasteValues
ElseIf Range("AdvanceWeek").Value = "Week 4" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C1538").PasteSpecial Paste:=xlPasteValues
ElseIf Range("AdvanceWeek").Value = "Week 5" Then
Range("Week1B").Copy
Sheets("Schedule - Results").Range("C2048").PasteSpecial Paste:=xlPasteValues
...
End Sub

(this continues on until 'ElseIf Range("AdvanceWeek").Value = "Week 31"....etc). So you can see I have the same code repeated 31 times. Let me know if this can be condensed to help with the calculating performance. Thanks

**Richard Buttrey** · 02-17-2014, 07:12 PM

It might be better if you upload your workbook. There may be better ways to achieve your results and may even be better ways to organise your data.

Tell us what the ultimate goal is here.

Also please note the forum rules about enclosing procedures in CODE tags

**Phily915** · 02-22-2014, 04:28 PM

im not sure if i uploaded my workbook it would be easy to follow. im making a college basketball simulation game, where it tracks all games and records, etc...