Sissyfoo
11-22-2005, 10:21 PM
Hello,
I have this macro here which is pretty bulky and could probably be made more efficient. It isn't really important or critical to my project buuuut I couldn't think if there was a more efficient/shorter way to code it what with being a n00b an' all. :)
I'd be much obliged if someone could cast an eye over it and suggest any improvements etc. Again, it isn't really important to my project but if ya don't ask questions, ya never learn.
Sub Perf_Import()
Dim SAQtr, SAMnth1, SAMnth2, SAMnth3 As Range 'Imported data stored on sheet("blah") for SA
Dim WAQtr, WAMnth1, WAMnth2, WAMnth3 As Range 'Imported data stored on sheet("blah") for WA
Dim NSWQtr, NSWMnth1, NSWMnth2, NSWMnth3 As Range 'Imported data stored on sheet("blah") for NSW
Dim QLDQtr, QLDMnth1, QLDMnth2, QLDMnth3 As Range 'Imported data stored on sheet("blah") for QLD
Dim VICQtr, VICMnth1, VICMnth2, VICMnth3 As Range 'Imported data stored on sheet("blah") for VIC
Dim PerfSA1, PerfSA2, PerfSA3, PerfSA4 As Range 'chart range of SA from Qtr-Mnth3
Dim PerfWA1, PerfWA2, PerfWA3, PerfWA4 As Range 'chart range of WA from Qtr-Mnth3
Dim PerfNSW1, PerfNSW2, PerfNSW3, PerfNSW4 As Range 'chart range of NSW from Qtr-Mnth3
Dim PerfQLD1, PerfQLD2, PerfQLD3, PerfQLD4 As Range 'chart range of QLD from Qtr-Mnth3
Dim PerfVIC1, PerfVIC2, PerfVIC3, PerfVIC4 As Range 'chart range of VIC from Qtr-Mnth3
Set SAQtr = Sheets("blah").Range("q1:s16")
Set SAMnth1 = Sheets("blah").Range("q18:s33")
Set SAMnth2 = Sheets("blah").Range("q35:s50")
Set SAMnth3 = Sheets("blah").Range("q52:s67")
Set WAQtr = Sheets("blah").Range("t1:v16")
Set WAMnth1 = Sheets("blah").Range("t18:v33")
Set WAMnth2 = Sheets("blah").Range("t35:v50")
Set WAMnth3 = Sheets("blah").Range("t52:v67")
Set NSWQtr = Sheets("blah").Range("w1:y16")
Set NSWMnth1 = Sheets("blah").Range("w18:y33")
Set NSWMnth2 = Sheets("blah").Range("w35:y50")
Set NSWMnth3 = Sheets("blah").Range("w52:y67")
Set QLDQtr = Sheets("blah").Range("z1:ab16")
Set QLDMnth1 = Sheets("blah").Range("z18:ab33")
Set QLDMnth2 = Sheets("blah").Range("z35:ab50")
Set QLDMnth3 = Sheets("blah").Range("z52:ab67")
Set VICQtr = Sheets("blah").Range("ac1:ae16")
Set VICMnth1 = Sheets("blah").Range("ac18:ae33")
Set VICMnth2 = Sheets("blah").Range("ac35:ae50")
Set VICMnth3 = Sheets("blah").Range("ac52:ae67")
Set PerfSA1 = Sheets("Performance Plan").Range("g43:i58")
Set PerfSA2 = Sheets("Performance Plan (2)").Range("g43:i58")
Set PerfSA3 = Sheets("Performance Plan (3)").Range("g43:i58")
Set PerfSA4 = Sheets("Performance Plan (4)").Range("g43:i58")
Set PerfWA1 = Sheets("Performance Plan").Range("g74:i89")
Set PerfWA2 = Sheets("Performance Plan (2)").Range("g74:i89")
Set PerfWA3 = Sheets("Performance Plan (3)").Range("g74:i89")
Set PerfWA4 = Sheets("Performance Plan (4)").Range("g74:i89")
Set PerfNSW1 = Sheets("Performance Plan").Range("g105:i120")
Set PerfNSW2 = Sheets("Performance Plan (2)").Range("g105:i120")
Set PerfNSW3 = Sheets("Performance Plan (3)").Range("g105:i120")
Set PerfNSW4 = Sheets("Performance Plan (4)").Range("g105:i120")
Set PerfQLD1 = Sheets("Performance Plan").Range("g136:i151")
Set PerfQLD2 = Sheets("Performance Plan (2)").Range("g136:i151")
Set PerfQLD3 = Sheets("Performance Plan (3)").Range("g136:i151")
Set PerfQLD4 = Sheets("Performance Plan (4)").Range("g136:i151")
Set PerfVIC1 = Sheets("Performance Plan").Range("g167:i182")
Set PerfVIC2 = Sheets("Performance Plan (2)").Range("g167:i182")
Set PerfVIC3 = Sheets("Performance Plan (3)").Range("g167:i182")
Set PerfVIC4 = Sheets("Performance Plan (4)").Range("g167:i182")
Sheets("blah").Activate
SAQtr.Copy
ActiveSheet.Paste Destination:=PerfSA1
SAMnth1.Copy
ActiveSheet.Paste Destination:=PerfSA2
SAMnth2.Copy
ActiveSheet.Paste Destination:=PerfSA3
SAMnth3.Copy
ActiveSheet.Paste Destination:=PerfSA4
WAQtr.Copy
ActiveSheet.Paste Destination:=PerfWA1
WAMnth1.Copy
ActiveSheet.Paste Destination:=PerfWA2
WAMnth2.Copy
ActiveSheet.Paste Destination:=PerfWA3
WAMnth3.Copy
ActiveSheet.Paste Destination:=PerfWA4
NSWQtr.Copy
ActiveSheet.Paste Destination:=PerfNSW1
NSWMnth1.Copy
ActiveSheet.Paste Destination:=PerfNSW2
NSWMnth2.Copy
ActiveSheet.Paste Destination:=PerfNSW3
NSWMnth3.Copy
ActiveSheet.Paste Destination:=PerfNSW4
QLDQtr.Copy
ActiveSheet.Paste Destination:=PerfQLD1
QLDMnth1.Copy
ActiveSheet.Paste Destination:=PerfQLD2
QLDMnth2.Copy
ActiveSheet.Paste Destination:=PerfQLD3
QLDMnth3.Copy
ActiveSheet.Paste Destination:=PerfQLD4
VICQtr.Copy
ActiveSheet.Paste Destination:=PerfVIC1
VICMnth1.Copy
ActiveSheet.Paste Destination:=PerfVIC2
VICMnth2.Copy
ActiveSheet.Paste Destination:=PerfVIC3
VICMnth3.Copy
ActiveSheet.Paste Destination:=PerfVIC4
Application.CutCopyMode = False
End Sub
It is basically a script to copy ranges from one sheet and split them up into ranges on 4 different sheets. I thought about using collections and arrays to scoop up the data but wasn't sure if it would make much difference to the speed or if it would impact my s/s at all.
Cheers,
James
I have this macro here which is pretty bulky and could probably be made more efficient. It isn't really important or critical to my project buuuut I couldn't think if there was a more efficient/shorter way to code it what with being a n00b an' all. :)
I'd be much obliged if someone could cast an eye over it and suggest any improvements etc. Again, it isn't really important to my project but if ya don't ask questions, ya never learn.
Sub Perf_Import()
Dim SAQtr, SAMnth1, SAMnth2, SAMnth3 As Range 'Imported data stored on sheet("blah") for SA
Dim WAQtr, WAMnth1, WAMnth2, WAMnth3 As Range 'Imported data stored on sheet("blah") for WA
Dim NSWQtr, NSWMnth1, NSWMnth2, NSWMnth3 As Range 'Imported data stored on sheet("blah") for NSW
Dim QLDQtr, QLDMnth1, QLDMnth2, QLDMnth3 As Range 'Imported data stored on sheet("blah") for QLD
Dim VICQtr, VICMnth1, VICMnth2, VICMnth3 As Range 'Imported data stored on sheet("blah") for VIC
Dim PerfSA1, PerfSA2, PerfSA3, PerfSA4 As Range 'chart range of SA from Qtr-Mnth3
Dim PerfWA1, PerfWA2, PerfWA3, PerfWA4 As Range 'chart range of WA from Qtr-Mnth3
Dim PerfNSW1, PerfNSW2, PerfNSW3, PerfNSW4 As Range 'chart range of NSW from Qtr-Mnth3
Dim PerfQLD1, PerfQLD2, PerfQLD3, PerfQLD4 As Range 'chart range of QLD from Qtr-Mnth3
Dim PerfVIC1, PerfVIC2, PerfVIC3, PerfVIC4 As Range 'chart range of VIC from Qtr-Mnth3
Set SAQtr = Sheets("blah").Range("q1:s16")
Set SAMnth1 = Sheets("blah").Range("q18:s33")
Set SAMnth2 = Sheets("blah").Range("q35:s50")
Set SAMnth3 = Sheets("blah").Range("q52:s67")
Set WAQtr = Sheets("blah").Range("t1:v16")
Set WAMnth1 = Sheets("blah").Range("t18:v33")
Set WAMnth2 = Sheets("blah").Range("t35:v50")
Set WAMnth3 = Sheets("blah").Range("t52:v67")
Set NSWQtr = Sheets("blah").Range("w1:y16")
Set NSWMnth1 = Sheets("blah").Range("w18:y33")
Set NSWMnth2 = Sheets("blah").Range("w35:y50")
Set NSWMnth3 = Sheets("blah").Range("w52:y67")
Set QLDQtr = Sheets("blah").Range("z1:ab16")
Set QLDMnth1 = Sheets("blah").Range("z18:ab33")
Set QLDMnth2 = Sheets("blah").Range("z35:ab50")
Set QLDMnth3 = Sheets("blah").Range("z52:ab67")
Set VICQtr = Sheets("blah").Range("ac1:ae16")
Set VICMnth1 = Sheets("blah").Range("ac18:ae33")
Set VICMnth2 = Sheets("blah").Range("ac35:ae50")
Set VICMnth3 = Sheets("blah").Range("ac52:ae67")
Set PerfSA1 = Sheets("Performance Plan").Range("g43:i58")
Set PerfSA2 = Sheets("Performance Plan (2)").Range("g43:i58")
Set PerfSA3 = Sheets("Performance Plan (3)").Range("g43:i58")
Set PerfSA4 = Sheets("Performance Plan (4)").Range("g43:i58")
Set PerfWA1 = Sheets("Performance Plan").Range("g74:i89")
Set PerfWA2 = Sheets("Performance Plan (2)").Range("g74:i89")
Set PerfWA3 = Sheets("Performance Plan (3)").Range("g74:i89")
Set PerfWA4 = Sheets("Performance Plan (4)").Range("g74:i89")
Set PerfNSW1 = Sheets("Performance Plan").Range("g105:i120")
Set PerfNSW2 = Sheets("Performance Plan (2)").Range("g105:i120")
Set PerfNSW3 = Sheets("Performance Plan (3)").Range("g105:i120")
Set PerfNSW4 = Sheets("Performance Plan (4)").Range("g105:i120")
Set PerfQLD1 = Sheets("Performance Plan").Range("g136:i151")
Set PerfQLD2 = Sheets("Performance Plan (2)").Range("g136:i151")
Set PerfQLD3 = Sheets("Performance Plan (3)").Range("g136:i151")
Set PerfQLD4 = Sheets("Performance Plan (4)").Range("g136:i151")
Set PerfVIC1 = Sheets("Performance Plan").Range("g167:i182")
Set PerfVIC2 = Sheets("Performance Plan (2)").Range("g167:i182")
Set PerfVIC3 = Sheets("Performance Plan (3)").Range("g167:i182")
Set PerfVIC4 = Sheets("Performance Plan (4)").Range("g167:i182")
Sheets("blah").Activate
SAQtr.Copy
ActiveSheet.Paste Destination:=PerfSA1
SAMnth1.Copy
ActiveSheet.Paste Destination:=PerfSA2
SAMnth2.Copy
ActiveSheet.Paste Destination:=PerfSA3
SAMnth3.Copy
ActiveSheet.Paste Destination:=PerfSA4
WAQtr.Copy
ActiveSheet.Paste Destination:=PerfWA1
WAMnth1.Copy
ActiveSheet.Paste Destination:=PerfWA2
WAMnth2.Copy
ActiveSheet.Paste Destination:=PerfWA3
WAMnth3.Copy
ActiveSheet.Paste Destination:=PerfWA4
NSWQtr.Copy
ActiveSheet.Paste Destination:=PerfNSW1
NSWMnth1.Copy
ActiveSheet.Paste Destination:=PerfNSW2
NSWMnth2.Copy
ActiveSheet.Paste Destination:=PerfNSW3
NSWMnth3.Copy
ActiveSheet.Paste Destination:=PerfNSW4
QLDQtr.Copy
ActiveSheet.Paste Destination:=PerfQLD1
QLDMnth1.Copy
ActiveSheet.Paste Destination:=PerfQLD2
QLDMnth2.Copy
ActiveSheet.Paste Destination:=PerfQLD3
QLDMnth3.Copy
ActiveSheet.Paste Destination:=PerfQLD4
VICQtr.Copy
ActiveSheet.Paste Destination:=PerfVIC1
VICMnth1.Copy
ActiveSheet.Paste Destination:=PerfVIC2
VICMnth2.Copy
ActiveSheet.Paste Destination:=PerfVIC3
VICMnth3.Copy
ActiveSheet.Paste Destination:=PerfVIC4
Application.CutCopyMode = False
End Sub
It is basically a script to copy ranges from one sheet and split them up into ranges on 4 different sheets. I thought about using collections and arrays to scoop up the data but wasn't sure if it would make much difference to the speed or if it would impact my s/s at all.
Cheers,
James