Variable not being stored in the code when sheet changes

Question

I'm quite new to coding and have encountered a probably very trivial issue. When I run the code line by line in the 'Trade_Sheet' tab, the variable Date_range is picked up correctly as a date previously copied, and updated for 7 days later. However the problem comes when I run the macro in the main tab 'Share_Calc_Tab' where the macro is situated. If I do so, it seems like the variable Date_range sets to 0, and while the rest of the operation is performed, the date will be missing.

The code is below:

Sub Audit_Trade()

    Dim Trade_Sheet As Worksheet
    Dim Share_Calc_Tab As Worksheet
    Dim lastrow As Long
    Dim Date_range As Date

    Set Trade_Sheet = ThisWorkbook.Worksheets("Trades")
    Set Share_Calc_Tab = ThisWorkbook.Worksheets("End Share Calc (ESC) GLOBAL")

    Application.ScreenUpdating = False

    With Trade_Sheet

        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Date_range = Cells(lastrow, 1).Offset(-27, 0).Value
        Cells(lastrow + 2, 1).Value = Date_range + 7

    End With

    Share_Calc_Tab.Activate
    Range("Trade_Instruction_Daily").Copy
    Trade_Sheet.Activate
    Cells(lastrow + 3, 1).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

    Share_Calc_Tab.Activate
    Range("B22").Select

    Application.ScreenUpdating = True

End Sub

Any help will be much appreciated. Thanks all!

If either of the answers helped you please consider [accepting them](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work). Accepting an answer rewards the contributor and helps those that follow you to find a working solution. — David Rushton, May 22 '18 at 11:44

Vityata · Accepted Answer · 2018-05-22T10:46:50.150

3

The problem is that you do not define the worksheet correctly. See the points(dots) here:

With Trade_Sheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    Date_range = .Cells(lastrow, 1).Offset(-27, 0).Value
    .Cells(lastrow + 2, 1).Value = Date_range + 7
End With

In your code, you are missing 2 of them:

Thus, the Cells() refers to the ActiveSheet, and not to the Trade_Sheet. In general, Activate and Select are considered a bad practice in vba, thus it is a good idea avoid them:

How to avoid using Select in Excel VBA.

For what is worth, this is probably the most common error in vba, thus you can be proud of yourself for reaching it. I guess that almost every VBA person has experienced it at least once.

edited May 22 '18 at 10:46

answered May 22 '18 at 10:35

Vityata

42,633
8
55
100

1

Thank you so much! Loved the explanation. – GCoxxx May 22 '18 at 11:03
@JohnyL - you can consider yourself lucky. This is probably the most common error in [tag:vba]. See it again here - https://stackoverflow.com/questions/50469103/data-going-to-wrong-workbook – Vityata May 22 '18 at 14:02
Oh... This is such a common error... I used to read [great book](http://www.wrox.com/WileyCDA/WroxTitle/Excel-2007-VBA-Programmer-s-Reference.productCd-0470046430.html) which explains this very moment. :) – JohnyL May 22 '18 at 16:24

score 1 · Answer 2 · answered May 22 '18 at 10:35

1

Perhaps problem is you did not have dots in this part of code?

 lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
 Date_range = .Cells(lastrow, 1).Offset(-27, 0).Value
 .Cells(lastrow + 2, 1).Value = Date_range + 7

answered May 22 '18 at 10:35

Radek Piekný

145
6

Thanks very much indeed! Concise and effective. – GCoxxx May 22 '18 at 11:03

Variable not being stored in the code when sheet changes

2 Answers2

Linked