CODEWITHSUNDEEP
vbaexcel
hernanavella
hernanavella

Reputation: 5552

How to restrict excel-vba macro to one sheet?

Following up to this question, I'm running a simple macro that I would like to run while still be able to work on other workbooks, or other sheets of the same workbook without the code to run on those. Here's the code

Sub Data_Recording()
'
' Data_Recording Macro
' Copy excel line with hyperlinks and paste values in first line of data     
  recording area, insterting and moving old data down.
'

'
    Rows("5:5").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B2:F2").Select
    Selection.Copy
    Range("B5").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
    SkipBlanks _
    :=False, Transpose:=False
    alertTime = Now + TimeValue("00:00:20")
    Application.OnTime alertTime, "Data_Recording"
End Sub

I tried putting the name of the workbook and sheet before every range or row selection, but I must be doing something wrong.

Thanks

Upvotes: 1

Views: 9565

Answers (4)

Stevo
Stevo

Reputation: 1

Public Sub MyMacro
    If ActiveSheet.Name < "Safe" Then Exit Sub

    'rest of your macro here
End Sub

Upvotes: -1

SierraOscar
SierraOscar

Reputation: 17637

You need to adapt the code so that all ranges are qualified with a Worksheet object, and that no .Select or .Activate statements are required.

Sub Data_Recording()

    With Workbooks("Workbook_Name_Here").Sheets("Sheet_Name_Here") '// Change as required
        .Rows(5).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("B5:F5").Value = .Range("B2:F2").Value '// No need to copy/paste
    End With

    Application.OnTime Now + TimeValue("00:00:20"), "Data_Recording"

End Sub

Upvotes: 4

Raugmor
Raugmor

Reputation: 566

try this:

ThisWorkbook.Sheets("You Sheet").Rows("5:5").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ThisWorkbook.Sheets("You Sheet").Range("B2:F2").Copy
ThisWorkbook("You Workbook").Sheets("You Sheet").Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
alertTime = Now + TimeValue("00:00:20")
Application.OnTime alertTime, "Data_Recording"

Upvotes: 0

Shalvin Abraham
Shalvin Abraham

Reputation: 458

Please make sure you put the above code in the Sheet scope.

In the VBA Editor, you can see 'Microsoft Excel Objects' inside Project Properties window. Expand it and double click on the sheet in which you want to work with (Ensure you selected correct workbook too)

In that code editor, put the same above code (No need to give sheet names or workbook names).

It should work fine.

Upvotes: 0

Related Questions