+ Reply to Thread
Results 1 to 13 of 13

Pass a variable from one sub-routine to another sub-routine

  1. #1
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Post Pass a variable from one sub-routine to another sub-routine

    Hey guys
    I need some help with this VB code.
    All I'm trying to do is create worksheets for each working day from a entered date for the month.
    Once worksheet is created I would like to copy from the "Source" sheet on each sheet created.
    Hence the "Source" sheet works as a template for individual sheet.
    See the code below, I'm getting errors, don't know how to fix it.

    Please Login or Register  to view this content.
    Last edited by arlu1201; 05-01-2013 at 02:45 AM. Reason: Corrected code tags.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi, gowtham_pec,

    please use code-tags to display the procedure to make it rea much easier.

    Why not copy the sheet source itself?

    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    Thanks Holger.
    This seems to work nice and easy.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi, gowtham_pec,

    you may need to add some basic error handling to the procedure just in case it would be run multiple times (like in the original code).

    Ciao,
    Holger

  5. #5
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Post Re: Pass a variable from one sub-routine to another sub-routine

    Just to add little more improvisation to this worksheet,
    Once all the daily sheets have been created, data is entered in each sheet, I would like to copy all the data from each sheet and copy it over to Master sheet.
    Note: Data can be several number of rows from each sheet.

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    forgot to mention, the copy over must be able to omit the sheets if its already done.

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi, gowtham_pec,

    the copy over must be able to omit the sheets if its already done.
    IŽd worked with a boolean statement in each sheet in one cell to show True if data has been copied and evaluate that in order to either copy or skip.

    Maybe you can have a look at Merge cells from all or some worksheets into one Master sheet for a coding to the problem.

    Ciao,
    Holger

  8. #8
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    Thanks for your help Holger.

  9. #9
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi Holger

    Need it more help with this.
    I have attached the workbook I have been working on.
    I have utilised your method for copying the daily sheets to a master sheet.

    1. But the daily sheets have 2 sets of data each of varying number of rows. So when I copy this is creating a gap between lists.
    Is there a way I can copy each list 'Ordered' & 'Invoiced' separately without creating blank rows.

    2. With the CopyDataWithoutHeaders sub-routine, I had to delete the 'MASTER' sheet before adding any data. Can I skip this step.
    I'm afraid if they delete the daily sheets by mistake, the master sheet atleast have the data. How can this be done.

    Your help is much appreciated.

    Thanks
    Gowtham_pec

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi, gowtham_pec,

    starting with the secon question you could change the code like this:
    Please Login or Register  to view this content.
    IŽd comment the other part in the code out first.

    For the first question: as you copy entire rows but have two different ranges on each sheet you can either break up the code into another small loop or two command checking each last row in each range and copy or stay with this procedure and delete the empty rows from Master for Orders near the end of the macro like
    Please Login or Register  to view this content.
    And AFAIK the codes you have donŽt belong behind Sheet Start but should go into a normal module.

    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    For the first code,
    I get an error at Max.
    It says sub or function not defined.

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pass a variable from one sub-routine to another sub-routine

    Hi, gowtham_pec,

    change to read
    Please Login or Register  to view this content.
    What about http://www.excelforum.com/excel-prog...worksheet.html Continuation from here because after 4 hours no answer has been placed (FYI: it 8:28 a.m. local time here on a workday off)?

    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    04-26-2013
    Location
    sydney
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Pass a variable from one sub-routine to another sub-routine

    Quote Originally Posted by HaHoBe View Post
    Hi, gowtham_pec,

    change to read
    Please Login or Register  to view this content.
    What about http://www.excelforum.com/excel-prog...worksheet.html Continuation from here because after 4 hours no answer has been placed (FYI: it 8:28 a.m. local time here on a workday off)?

    Ciao,
    Holger
    Thanks Holger.
    Yes I was desperately had to get an answer, due to deadline restrictions yesterday.
    Thanks for your continued help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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