Frequently Used code

[jayherring86]

Hey Everyone,

I'm using a workbook with lots of macros doing all sorts of magic for me. Aside from "update this" and "look up that" here and there, by far the most frequent type of macro is one that moves information from the active sheet to a "transactions" sheet - this is to consolidate the data from many different sources and helps create inventory and analysis. I'm quite proud of what i've done, really (and acknowledge that you guys on here helped me learn the new stuff i needed).

Here's the question,

There are 4 worksheets for data entry and each one currently has it's own sub, assigned to a button that processes the entries into the transactions. The code does this:

Please Login or Register  to view this content.

This is an example of the code but there are at least 3 other sheets that feature very similar subs. I've been reading about good practice in programming and it says that repeatedly used code should be put into it's own private sub and called by the other subs that use it. The problem is that there are things in this code that are variable, such as the row number where the data starts (in "entry" it is row 28:77, but "online" is range 9:86). I understand that the sub NAME () brackets can be used to "pass" the sub some variables, but haven't been able to properly consolidate this code into it's own private sub.

Where, or how, do i define a variable to identify the first row that the macro looks at in each sheet? I've tried this sub as the one that activates when the confirm button is clicked:

Please Login or Register  to view this content.

but that doesn't work for anything!

Help is appreciated.

Edited to add: p.s I know that the consolidated code will also need to have a defined variable to know which sheet to switch back to - as the code above keeps switching to the "entry" sheet, i'm assuming that passing the variable for the first row, and the sheet of origin will be the same.

[mehmetcik]

This works:-

Please Login or Register  to view this content.

[jayherring86]

Ah yes thank you. I think it was the structure i was getting stuck with then since i didn't tell the called sub what to do with the values once they had been passed over.

Thanks for your help. Just to check, there won't be a problem using a variable like this:

Please Login or Register  to view this content.

will there? Or maybe i'll have to put " " ?

[mehmetcik]

if you want to reference the sheet that called the subroutine then you need to either:-

a: Pass the Sheet Name to the Subroutine

or

b: Save the Sheetname as a public variable

The following works but only if I don't specify the variable type

Please Login or Register  to view this content.

[jayherring86]

Again thanks. Thats fantastic