Results 1 to 5 of 5

Frequently Used code

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-23-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    139

    Frequently Used code

    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:

        Application.ScreenUpdating = False
            
            
        
        'add transaction info for date/time etc.
        
        
        Sheets("entry").Range("AB28:ae77").Value = Sheets("Admin").Range("J8:m8").Value
        
        Sheets("admin").Range("j6").Value = Sheets("admin").Range("j6").Value + 1
        
        Sheets("entry").Activate
        
        Range("B28:Ai77").Copy
        
        Sheets("Transactions").Select
        
            If ActiveSheet.FilterMode Then
                ActiveSheet.ShowAllData
            End If
            
            
        Range("A1").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Admin").Select
        Range("L6").Copy
        Range("J6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Entry").Select
        Call EntryCancel
            Application.ScreenUpdating = True
    
    
    End Sub
    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:

    sub Entry_Confirm()
    
    set EntryFirstRow = 28
    
    call Transaction_Process(entryfirstrow)
    
    end sub
    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.
    Last edited by jayherring86; 05-03-2015 at 06:24 AM.
    IF("helping me", "thanks", "need more detail?")

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Finding the most frequently used code for different conditions
    By raynejan in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2013, 10:39 PM
  2. most frequently occuring set
    By apok9f in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-25-2013, 04:29 AM
  3. Excel Freezes frequently
    By priyadva in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-24-2013, 09:16 AM
  4. Most frequently occurring text
    By Deanomcbeano in forum Excel General
    Replies: 4
    Last Post: 08-12-2009, 08:58 AM
  5. most frequently occurring value
    By Pivotrend in forum Excel General
    Replies: 3
    Last Post: 12-23-2005, 08:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1