+ Reply to Thread
Results 1 to 2 of 2

Restrict macro to one/native workbook

Hybrid View

  1. #1
    Registered User
    Join Date
    08-20-2016
    Location
    Belgrade
    MS-Off Ver
    Windows 7 64-bit, Excel 2007
    Posts
    22

    Restrict macro to one/native workbook

    Hello.

    I have a problem with code which runs periodically (every 30 seconds).

    Basically, the code compares data from two Sheets (at the same Workbook) and then paste data (under certain conditions) from Sheet4 to Sheet1.

    However, when I open some other Workbook (window on that "other" Workbook is active) and I start with my new project, the code from previous Workbook then paste results into that new Workbook also!?

    So, this is what happened and what I actually want to do:

    - I open Workbook 1 with the macro in it and it runs perfectly (great!)

    - I open WB2 (Workbook1 is still open, macro still runs) and when WB2 is active then macro from WB1 start to paste data into WB2 (problem)

    - I want macro from WB1 to keep working only in WB1 no matter the active workbook

    Here is the code:

    Sub Salary()
    Dim Rng As Range, cell As Range, lr As Long, ws As Worksheet, sh As Worksheet, product_name As String
    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set sh = ThisWorkbook.Sheets("Sheet4")
    
    For Each Rng In sh.Range("B3:B" & sh.Range("B" & Rows.Count).End(xlUp).Row)
    product_name = Rng.Value
    For Each cell In ws.Range("A3:X3")
    If InStr(1, cell, Rng.Value) > 0 Then
    If Cells(ws.Cells(Rows.Count, cell.Column).End(xlUp).Row, cell.Column).Value <> Rng.Offset(, 2).Value Then Cells(ws.Cells(Rows.Count, cell.Column).End(xlUp).Row + 1, cell.Column).Value = Rng.Offset(, 2).Value
    GoTo nextrng
    End If
    Next cell
    nextrng:
    Next Rng
    Application.OnTime Now + TimeValue("00:00:30"), "Salary"
    End Sub

  2. #2
    Forum Expert
    Join Date
    06-25-2009
    Location
    Sofia, Bulgaria, EU
    MS-Off Ver
    Excel 2003-2013
    Posts
    1,290

    Re: Restrict macro to one/native workbook

    Not able to test, but this should do the job:

    Sub Salary()
        Dim Rng As Range, cell As Range, lr As Long, ws As Worksheet, sh As Worksheet, product_name As String
        Set ws = ThisWorkbook.Sheets("Sheet1")
        Set sh = ThisWorkbook.Sheets("Sheet4")
    
        For Each Rng In sh.Range("B3:B" & sh.Range("B" & Rows.Count).End(xlUp).Row)
            product_name = Rng.Value
            For Each cell In ws.Range("A3:X3")
                If InStr(1, cell, Rng.Value) > 0 Then
                    With ws
                        If .Cells(.Cells(Rows.Count, cell.Column).End(xlUp).Row, cell.Column).Value <> Rng.Offset(, 2).Value Then
                            .Cells(.Cells(Rows.Count, cell.Column).End(xlUp).Row + 1, cell.Column).Value = Rng.Offset(, 2).Value
                        End If
                    End With
                        GoTo nextrng
                    End If
                Next cell
    nextrng:
            Next Rng
            Application.OnTime Now + TimeValue("00:00:30"), "Salary"
        End Sub
    If you are pleased with a member's answer then use the Star icon to rate it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to restrict code to current workbook
    By troygeri in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-03-2014, 11:17 PM
  2. [SOLVED] Restrict macro to one workbook
    By sambashir in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-09-2013, 03:17 PM
  3. Replies: 0
    Last Post: 09-10-2012, 04:51 AM
  4. [SOLVED] Workbook(s) With ALL Native Worksheet Function Samples
    By Dennis7849 in forum The Water Cooler
    Replies: 11
    Last Post: 04-01-2012, 07:43 PM
  5. Restrict Access to Workbook
    By nguyeda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-26-2009, 06:23 AM
  6. Restrict user from over-writing workbook
    By TwoyTaylor in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2008, 11:23 PM
  7. Protection - restrict macro exposure to the workbook, VBAProject
    By JB2005 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-15-2005, 09:45 AM

Tags for this Thread

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