+ Reply to Thread
Results 1 to 5 of 5

new to vba, module or worksheet code?

  1. #1
    Registered User
    Join Date
    10-19-2010
    Location
    .
    MS-Off Ver
    .
    Posts
    10

    new to vba, module or worksheet code?

    I have a macro that runs in a specific worksheet, and I would like to extend it to other worksheets in the same workbook. Basically it extracts data from the worksheet and puts it into a text file. So if anyone copies anything with a similar format into other worksheets, I want the macro to be able to run on those worksheets as well. But the macro always keeps referencing the original worksheet it was coded in. I searched around a bit and saw that you can put code into a "module". I'm not entirely sure what the difference is, I thought the module code can be used by the whole workbook rather than a single worksheet. Anyway, I have a button on the worksheet that has the macro assigned to it so it runs when clicked. How do I assign to macro from the module to the button? If anyone has a solution to my problem of making the macro to run on all the worksheets in the workbook, it would be great. Just to clarify I DON'T want it to loop through all the worksheets extracting data. I want it so that I can run the same macro individually on each worksheet and it extracts data from the worksheet I am on, not the original.

    Thanks.

  2. #2
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: new to vba, module or worksheet code?

    This can be achieved quite easily c2q, but it would help to have an example workbook posted so we can view your code and modify it accordingly.

  3. #3
    Registered User
    Join Date
    10-19-2010
    Location
    .
    MS-Off Ver
    .
    Posts
    10

    Re: new to vba, module or worksheet code?

    Here you go:

    Please Login or Register  to view this content.
    So the way it works is that it takes all the data from the cells needed and puts them into a "dummy worksheet" and then copies that to a new workbook, saves it as a text file and deletes the dummy worksheet and workbook so everything stays the same.

  4. #4
    Registered User
    Join Date
    10-19-2010
    Location
    .
    MS-Off Ver
    .
    Posts
    10

    Re: new to vba, module or worksheet code?

    Okay I think I figured it out. It seems to work now, I had to specify the active sheet and then add the active sheet in front of the Cells(x,y).Value in order to make it paste data from the sheet I am on.

  5. #5
    Forum Contributor
    Join Date
    05-25-2012
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    194

    Re: new to vba, module or worksheet code?

    I'm assuming you have some embedded button on your worksheet. These buttons are similar to vba modules in the sense that they can house code separate from the vba editor and can que up based on a wide variety of triggering events.

    Based on what I see, the code looks more than capable to run on varying sheets (assuming the sheets contain similarly formatted data) as any cells not pertaining to the dummy worksheet do not reference a specific worksheet and therefore would default to the activeworksheet - i.e. Cells(1, 2).Value is the same as saying ActiveSheet.Cells(1, 2).Value.

    So your solutions to use it on multiple sheets are to either copy/paste this button with the referenced macro to the sheets you want to use it on or create a prompt that allows you to input what sheet you'd like to use it with. The code would look something along the lines of;

    Please Login or Register  to view this content.
    Let me know if you have any other questions.

    **edit - seems like you figured out what I just explained - Although, as noted above, you do not have to add "activesheet" as long as you are simply executing this code from the workbook.
    Last edited by Medpack; 08-09-2012 at 07:35 PM.

+ 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