2

I've got some bits of code that is repeating itself many times, and I'm also using them on several "group-runs", so I'm re-using my code a lot. So, I'm trying to declare some values in such a way that I can change this only one time at the top of each run. Below I'm trying to declare a sheet name and a row number at the top, but I'm only getting a "variable not defined" error. Anyone that can help me how to declare these two values correct - and how to put them correct in to my code (other declarations is already done, and working).

Dim grsh, ntid As String
grsh = s1g1
ntid = 2289

Application.StatusBar = "Now updating: & grsh &   (new report data)."                   'grsh

        lr = sh_2_fravær.Range("A" & Rows.Count).End(xlUp).Row
        sh_2_fravær.Range("A3:E" & lr).ClearContents

            Set wb_Fravær = Workbooks("Fravær.xlsm")
            lr = wb_Fravær.Sheets("grsh").Range("A" & Rows.Count).End(xlUp).Row         'grsh
            wb_Fravær.Sheets("grsh").Range("A4:D" & lr).Copy                            'grsh

                sh_2_fravær.Range("A3").PasteSpecial
                Application.CutCopyMode = False



        lr = sh_11_tid.Range("A" & Rows.Count).End(xlUp).Row
        sh_11_tid.Range("A&ntid:L" & lr).ClearContents                                   'ntid

            Set wb_Tidsbruk = Workbooks("Tidsbruk.xlsm")
            lr = wb_Tidsbruk.Sheets("grsh").Range("A" & Rows.Count).End(xlUp).Row        'grsh
            wb_Tidsbruk.Sheets("grsh").Range("A5:I" & lr).Copy                           'grsh

                sh_11_tid.Range("A&ntid").PasteSpecial                                   'ntid
                Application.CutCopyMode = False
Improve this question
6
  • 2
    1. Dim grsh as string, ntid As Long 2. lr = wb_Fravær.Sheets(grsh).Range("A" & Rows.Count).End(xlUp).Row 3. sh_11_tid.Range("A" & ntid & ":L" & lr).ClearContents 4. lr = wb_Tidsbruk.Sheets(grsh).Range("A" & Rows.Count).End(xlUp).Row 5. wb_Tidsbruk.Sheets(grsh).Range("A5:I" & lr).Copy 6. sh_11_tid.Range("A" & ntid).PasteSpecial Commented May 1, 2018 at 7:09
  • 1
    Also: Application.StatusBar = "Now updating: "& grsh & " (new report data)." Commented May 1, 2018 at 7:13
  • Forgot grsh = "s1g1" Commented May 1, 2018 at 7:15
  • Updated my code, but I'm still getting a "Variable not defined" error, marking out line grsh = s1g1 at the top. Commented May 1, 2018 at 7:23
  • With grsh = "s1g1", it worked just fine! Thanks a lot for helping out. Commented May 1, 2018 at 7:26

1 Answer 1

Reset to default
4

Looks primarily like basic syntax errors.

    Dim grsh As String, ntid As Long          '<~~ fixed
    grsh = "s1g1"                             '<~~ fixed
    ntid = 2289

    Application.StatusBar = "Now updating: " & grsh & " (new report data)."   '<~~ fixed

    lr = sh_2_fravær.Range("A" & Rows.Count).End(xlUp).Row
    sh_2_fravær.Range("A3:E" & lr).ClearContents

    Set wb_Fravær = Workbooks("Fravær.xlsm")
    lr = wb_Fravær.Worksheets(grsh).Range("A" & Rows.Count).End(xlUp).Row    '<~~ fixed
    wb_Fravær.Worksheets(grsh).Range("A4:D" & lr).Copy                       '<~~ fixed

    sh_2_fravær.Range("A3").PasteSpecial
    Application.CutCopyMode = False

    lr = sh_11_tid.Range("A" & Rows.Count).End(xlUp).Row
    sh_11_tid.Range("A" & ntid & ":L" & lr).ClearContents                    '<~~ fixed

    Set wb_Tidsbruk = Workbooks("Tidsbruk.xlsm")
    lr = wb_Tidsbruk.Worksheets(grsh).Range("A" & Rows.Count).End(xlUp).Row   '<~~ fixed
    wb_Tidsbruk.Worksheets(grsh).Range("A5:I" & lr).Copy                      '<~~ fixed

    sh_11_tid.Range("A" & ntid).PasteSpecial                                  '<~~ fixed
    Application.CutCopyMode = False
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.