0

I am trying to store the history of a number of cells in a worksheet. I created a macro to do this, which works by clicking a button, runs the macro and stores the data in a different part of the sheet (that I've hidden). The problem is that I want to be able to do this in multiple sheets. However the count keeps incrementing regardless of which sheet I'm on. therefore where you store the history will have moved a position if the button is clicked (i.e macro is run). I know it is because I am using a static integer as the count, and that every time the macro is run it will increment. How do I make the macro sheet independent. What I want is, when I click the button/run the macro, the data will be stored in sequence for that sheet.

Code

Sub Historic_Data()
 Static xCount As Integer

    Range("J3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AP4").Offset(xCount, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AQ4").Offset(xCount, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    xCount = xCount + 1
    Range("K1").Select
    Application.CutCopyMode = False

End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.