Specifying what worksheet a macro should be executed on?

[sodonnell44]

I'm putting data off the internet and refreshing it in real time. The problem I'm going to run into is that occasionally the website I'm pulling from will but unwanted characters in a certain cell so I was going to use a macro to circumvent this if it happens by doing a little copy, text to columns, replace b/c I'm also using vlookup to organize all if it. In any case, I want to be able to execute the macro on Sheet4 while remaining on Sheet1. Listed below is the macro. Any help would be greatly appreciated. Thanks


Sub Macro3()
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+m
'


Sheet4.Range("E91").Select
Selection.Copy
Sheet4.Range("P78").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Sheet4.Range("P78"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Sheet4.Rows("91:91").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheet4.Range("P78").Select
Selection.Copy
Sheet4.Range("E91").Select
ActiveSheet.Paste
Sheet4.Range("F92:M92").Select
Application.CutCopyMode = False
Selection.Copy
Sheet4.Range("F91").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheet4.Rows("92:92").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
End Sub

 

[Mike Blackman]

Hi,

How does this work for you?

Sub test()

    Application.ScreenUpdating = False

    With Sheet4
        .Range("E91").Copy
        With .Range("P78")
            .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
            .TextToColumns Destination:=.Range("P78"), _
                           DataType:=xlDelimited, _
                           TextQualifier:=xlDoubleQuote, _
                           ConsecutiveDelimiter:=False, _
                           Tab:=True, _
                           Semicolon:=False, _
                           Comma:=False, _
                           Space:=False, _
                           Other:=True, _
                           OtherChar:="-", _
                           FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
                           TrailingMinusNumbers:=True
        End With
        .Rows("91").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        .Range("P78").Copy
        .Range("E91").Paste
        .Range("F92:M92").Copy
        .Range("F91").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .Rows("92").Delete Shift:=xlUp
    End With
    
    Application.ScreenUpdating = True

End Sub

 

[sodonnell44]

Thanks for the reply Mikey!

I'm getting an debugging error close to the bottom. At - .Range("E91").Paste

I know it pasting over something, but that isn't a problem when we use it on the active worksheet. Anyways, thanks for assistance with this. If you can think of anything that could be causing that, let me know.