CODEWITHSUNDEEP
vbaexcel
Yasmine Nouri
Yasmine Nouri

Reputation: 103

macro that auto-executes when sheet is opened

Is it possible that my macro (update () ) auto-executes everytime the excel file is opened. The code below doesn't work well. Thanks

Private Sub Workbook_Open()

    Run "update"

End Sub

Option Explicit

Sub update()

    Dim rng As Range
    Dim Sh As String, Cl As String
    Dim ws As Worksheet
    Dim i As Integer, ncol As Integer
    Dim Row1 As String

    ncol = Range("B1:O1").Columns.Count

    For i = 1 To ncol
        Set ws = ThisWorkbook.Sheets("sheet1")

        With ws
            Row1 = .Cells(1, i).Value

            If Len(Row1) > 0 Then
                Sh = Split(Row1, "'!")(0)
                Cl = Split(Row1, "'!")(1)
                Set rng = ThisWorkbook.Sheets(Sh).Range(Cl)

                'Here you were always refering to cell A2 not moving through the values which was the main problem.
                rng.Value = .Cells(2, i).Value
            End If
        End With
    Next i
End Sub

Upvotes: 2

Views: 141

Answers (1)

whytheq
whytheq

Reputation: 35597

As mentioned in the comments. Move the following:

Private Sub Workbook_Open()

    Run "update"

End Sub

To here:

enter image description here

As mentioned by Siddharth there is another way to get a macro to run on the file open event and that is to simply to give it the following signature:

Sub Auto_Open

Also, personally I'd probably not call a sub-routine just "update" as it is quite close to lots of reserved words - I'd go for something like "updateSomething". This is just personal choice.

Upvotes: 3

Related Questions