PDA

View Full Version : Solved: Questions about calling procedures to shorten code



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

lucas
01-09-2009, 07:33 AM
Hi D,
Welcome to the forum. I noticed a lot of people have viewed your post but no responses. Maybe you could post a dummy workbook with before and after results so we can understand your project a little better.

I've read it a couple of times and cannot seem to wrap my head around your requirement.

Bob Phillips
01-09-2009, 07:52 AM
As one who looke and passed, I think he wants a different approach Steve. He seems to be asking a generalised question, but giving a specific example. There seems little scope to put the given code in to called modules, which he would see if he understood the concept. So it looks like three steps back to me.

glaukopisign
01-11-2009, 06:30 PM
11419

Hi Lucas.

Thank you very much for responding - appreciated :-).

I posted the code because it said in the 'how to get help' info that you should always post the code so people can understand what you want....obviously not very successful in this case.

XLD is right in that my question was general (although s/he has a particularly RTFM-70s helpdesk way of pointing it out!).

The gist is that:
if I use very similar pieces of code multiple times, is a way of 'calling' it without having to write/copy the code again and again?

For example, I will use this piece of code 12 times to create the completed summary sheet.

'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

I have attached the 'dummy' workbook. You can see I have only completed one line, as the macro currently only summarizes the first of four possible different entries under the same generic categories.

Hope that makes things clearer, and again thank you very much for your time.

Cheers

D(eb)

glaukopisign
01-11-2009, 11:33 PM
Hi.

Firstly, apologies for not saying up front that I had posted a similar question as an add-on to a previous post in a different forum. This was because I thought the person there would be (justifiably) not paying attention to that thread anymore. He was, and has helped me solve this problem, for which I am very grateful.

"Lucas", thanks again for trying to help me. It is appreciated.

I attach the link to my original posts at techsupport forum - (not actually a link, because I can't post it, just the tail of the URL): /microsoft-support/microsoft-office-support/313762-macro-transferring-data-many-worksheets-one.html#post1908913

Cheers

D

lucas
01-12-2009, 08:23 AM
Hi Deb, It would have been really considerate of you to have provided that information in the beginning.

Please read our faq on cross posting before posting in this forum again.