CODEWITHSUNDEEP
vbaexcel
Laura Morris
Laura Morris

Reputation: 222

Excel VBA always runs on visible sheet instead of referenced sheet

I am writing a macro which will update a specific sheet in a workbook (summary). It needs to insert a row and add values to it but it needs to always update the summary sheet no matter which worksheet was active when the macro was run. At present it runs on whichever sheet was active. I have tried explicitly naming the sheet everytime I give a range or cell reference. (I'm fairly new to macros so recorded a macro to give me a starting point hence the long winded way of selecting and copying).

Sub PasteValuesSummary()

     Dim LookupValue As String

     Dim LookupRange As Range
     Dim ws As Worksheet

     Set ws = ThisWorkbook.Sheets("Summary")

     Set LookupRange = Sheets("Summary").Range("B1:B1000")

     LookupValue = Sheets("LastUpdate").Range("A1").Value

     If Not IsError(Application.Match(LookupValue, LookupRange, 0)) Then

        Sheets("Summary").Select
        Sheets("Summary").Rows("4:4").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Sheets("Summary").Range("B4").Select
        Sheets("Summary").Range("B4") = "=LastUpdate!R[-3]C[-1]"
        Sheets("Summary").Range("C2:AP2").Select
        Selection.Copy
        ActiveWindow.ScrollColumn = 1
        Sheets("Summary").Range("C4").Select
        Sheets("Summary").Paste
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Summary").Range("B4:AP4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

     End If

End Sub

Upvotes: 0

Views: 315

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

Try this code. I take it LookupValue should be a date?

Sub PasteValuesSummary()

     Dim LookupValue As Date
     Dim rFound As Range

     Dim LookupRange As Range
     Dim ws As Worksheet

     Set ws = ThisWorkbook.Worksheets("Summary")

     Set LookupRange = ws.Range("B1:B1000")

     LookupValue = ThisWorkbook.Worksheets("LastUpdate").Range("A1").Value

     Set rFound = LookupRange.Find(What:=LookupValue, SearchFormat:=True)
     If Not rFound Is Nothing Then
        With ws 'Search help on 'With' keyword.
            .Rows("4:4").Insert shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            .Range("B4").FormulaR1C1 = "=LastUpdate!R[-3]C[-1]"
            .Range("C2:AP2").Copy Destination:=.Range("C4")

            'This is just removing the formula and leaving the values.
            .Range("B4:AP4").Copy
            .Range("B4:AP4").PasteSpecial Paste:=xlPasteValues 'No need to add the other arguments - they're default.
        End With
    End If

End Sub

Upvotes: 2

Related Questions