glaukopisign
01-08-2009, 09:59 PM
Hello.
I have just been trying to read 'optimize your code' after failing to put in some similar 'shortcuts' in my increasingly unwieldly code in Excel 03. I am not a programmer and I clearly do not understand how 'calling' methods works, so I couldn't understand the language in the article.
I tried to just paste the bit of my code I wanted to use more than once and called it in my main code. This did not work (perhaps unsuprisingly). Is it possible to please explain a little bit about what 'bits' of the main code need to be in a called code for it to work?
I am creating a summary sheet from multiple worksheets which has 4 categories (offsets) with 3 subcategories (protected matters) each. Each catergory and sub category has the same attributes. Part of current code (Offset 1) pasted below. Offsets 2,3,4 have the same generic categories, everything else is +3 rows (ie if referenced cell is h3 in offset 1, it will be h6 in offset 2):
Thanks in advance for any help you can give.
Option Explicit
Sub ProtectedMatterSummary()
Dim Counter As Long
Dim Source As Worksheet
Dim Dest As Worksheet
'This macro creates a summary sheet with only one column of protected matters (instead of three)
Application.ScreenUpdating = False
' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True
' Add a new summary worksheet.
Set Dest = ActiveWorkbook.Worksheets.Add
Dest.Name = "Summary"
'Loop through each worksheet, copying and pasting all the information onto the summary sheet
For Counter = 1 To ActiveWorkbook.Worksheets.Count
Set Source = ActiveWorkbook.Worksheets(Counter)
'make sure that the data from the instructions, data and summary worksheets are not included.
If Not (Source.Name = Dest.Name) Then
If Not (Source.Name = "Instructions") Then
If Not (Source.Name = "Data") Then
'Offset 1
'protected matter 1
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
Source.Range("H6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
'protected matter 2
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
Source.Range("H7").Copy
'protected matter 3
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
'cell - protected matter 3
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
Source.Range("H8").Copy
Thanks again
D
I have just been trying to read 'optimize your code' after failing to put in some similar 'shortcuts' in my increasingly unwieldly code in Excel 03. I am not a programmer and I clearly do not understand how 'calling' methods works, so I couldn't understand the language in the article.
I tried to just paste the bit of my code I wanted to use more than once and called it in my main code. This did not work (perhaps unsuprisingly). Is it possible to please explain a little bit about what 'bits' of the main code need to be in a called code for it to work?
I am creating a summary sheet from multiple worksheets which has 4 categories (offsets) with 3 subcategories (protected matters) each. Each catergory and sub category has the same attributes. Part of current code (Offset 1) pasted below. Offsets 2,3,4 have the same generic categories, everything else is +3 rows (ie if referenced cell is h3 in offset 1, it will be h6 in offset 2):
Thanks in advance for any help you can give.
Option Explicit
Sub ProtectedMatterSummary()
Dim Counter As Long
Dim Source As Worksheet
Dim Dest As Worksheet
'This macro creates a summary sheet with only one column of protected matters (instead of three)
Application.ScreenUpdating = False
' Delete the summary sheet if it exists.
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("Summary").Delete
On Error GoTo 0
Application.DisplayAlerts = True
' Add a new summary worksheet.
Set Dest = ActiveWorkbook.Worksheets.Add
Dest.Name = "Summary"
'Loop through each worksheet, copying and pasting all the information onto the summary sheet
For Counter = 1 To ActiveWorkbook.Worksheets.Count
Set Source = ActiveWorkbook.Worksheets(Counter)
'make sure that the data from the instructions, data and summary worksheets are not included.
If Not (Source.Name = Dest.Name) Then
If Not (Source.Name = "Instructions") Then
If Not (Source.Name = "Data") Then
'Offset 1
'protected matter 1
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
Source.Range("H6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
'protected matter 2
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
Source.Range("H7").Copy
'protected matter 3
'Generic to project categories - eg project name and epbc number
Source.Range("C3:F3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count + 1, 1).PasteSpecial xlPasteAll
Source.Range("I3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 5).PasteSpecial xlPasteAll
Source.Range("J3").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 6).PasteSpecial xlPasteAll
'copy "Offset specific" categories - eg type of offset
Source.Range("C6:F6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 7).PasteSpecial xlPasteValues
Source.Range("I6:K6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 13).PasteSpecial xlPasteValues
Source.Range("M6:o6").Copy
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 14).PasteSpecial xlPasteValues
'cell - protected matter 3
Dest.Cells(Dest.Range("A1").CurrentRegion.Rows.Count, 17).PasteSpecial xlPasteValues
Source.Range("H8").Copy
Thanks again
D