Help with VBA

[Mike7777]

I am working on a spread sheet that in one specific cell(we'll say Sheet1 A1) I need it to display another cell on another page(Sheet2 B36). Thats easy enough, but then at each calculation I need the cell above the original cell that was being displayed to be displayed (so now its displaying Sheet2 B35 in Sheet1 A1) and at each calculation after go up one cell each time.
Is this possible? I've tried googling this and I can't find anything.
Thanks for any help
Much Appreciated
Mike

edit, There will be no formula in Sheet1 A1 I just need it to display the value of Sheet 2 then up one per calculation.

 

[offthelip]

try this code. I am note sure how you want to initialise the variable Currow, I ahve put in the workbook open and the worksheet activate subroutines:
Currow must be declared right at the top of the Sheet1 module
in the "sheet1" module

Public currow


Private Sub Worksheet_Activate()
currow = 36
End Sub


Private Sub Worksheet_Calculate()
If currow > 2 Then
 currow = currow - 1
End If
Cells(1, 1) = Worksheets("Sheet2").Range("B" & currow)


End Sub

in THISWORKBOOK module:

Private Sub Workbook_Open()
currow = 36
End Sub

 

[Mike7777]

Thank you offthelip for your help I probably should have mentioned I have vba for Sheet1 for another task I needed done can I integrate your vba with what i have? Can you show me how?
###
Sub NETWORK()
'
' NETWORK Macro
'


'
Dim k As Integer
k = 0


For i = 1 To 1000
Calculate
Range("V8:AC33").Select
Application.CutCopyMode = False
Selection.Copy
Range("B8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
If Cells(1, 1) = 1 Then k = k + 1




Next i

Cells(2, 1) = k


End Sub
###

Thank you

 

[offthelip]

I am not sure what you want:
I can see what your code does. ( although I don't understand why you copy the same range to the same place 1000 times)
What do you mean by "integrate" What do you want all of your final code to do?

 

[Kenneth Hobson]

My version keeps track of the last move in Sheet2!Z1.

Right click your sheet's Tab, View Code, and paste:

Private Sub Worksheet_Calculate()
    Dim c As Integer
    
    c = Application.Calculation
    Application.Calculation = xlCalculationManual
    
    With Worksheets("Sheet2")
        If .[Z1] = "" Then
            .[Z1] = "B36"
            Else
            'On Error Resume Next
            .[Z1] = .Range(.[Z1]).Offset(-1).Address
        End If
        [A1] = .Range(.[Z1])
    End With
    
    Application.Calculation = c
End Sub

 

[Mike7777]

I am not sure what you want:
I can see what your code does. ( although I don't understand why you copy the same range to the same place 1000 times)
What do you mean by "integrate" What do you want all of your final code to do?

Its pasting to the left and its a calculation that has to be updated in this manner. I have it set to 1000 to do all those calculations for me without hitting F9 1000 times. Its just a time saver for what I'm working on.
And yes I was wondering if what you sent could be put in mine?
Thanks

 

[Mike7777]

I like that Kenneth Thank you.
Can that be put into my vba I already have?
Thanks again.
Mike

 

[Mike7777]

Sub NETWORK()
'
' NETWORK Macro
'
 
'
Dim k As Integer
k = 0
 
For i = 1 To 1000
Calculate
    Dim c As Integer
   
    c = Application.Calculation
    Application.Calculation = xlCalculationManual
   
    With Worksheets("Sheet2")
        If .[Z1] = "" Then
            .[Z1] = "B36"
            Else
            'On Error Resume Next
            .[Z1] = .Range(.[Z1]).Offset(-1).Address
        End If
        [A1] = .Range(.[Z1])
    End With
   
    Application.Calculation = c
Range("V8:AC33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
If Cells(1, 1) = 1 Then k = k + 1
 
 
Next i
  
 Cells(2, 1) = k
 
   
End Sub

Something like this? I please excuse me I dont know vba? But I'm working on it.
Could you show me how this would work?

Thank You

 

[Kenneth Hobson]

The code that we posted runs when a calculation event occurs on that Sheet.

Your Sub, is public so it would go in a Module. You could easily put my sub into yours or call like any "sub"routine if you made it into a public sub and put into a Module. OF course then it only fires when you call it. e.g.

Sub Main()
    Sub1
    Sub2
End Sub

Sub Sub1()
    MsgBox "1"
End Sub

Sub Sub2()
    MsgBox "2"
End Sub

As shown in post #8 , after i=36, there would be no further changes to A1. The loop would continue to 1000.

I am not clear why you want to run calculate 1000 times. If you have a volatile function on the sheet and make changes with calculation mode as automatic, calculation event fires. Most people turn off calculation as I did so that speed is better.

I think explaining in more detail what you goals are might help us best help you. The calculation deal might be not be needed at all.

 

[Mike7777]

The code that we posted runs when a calculation event occurs on that Sheet.

Your Sub, is public so it would go in a Module. You could easily put my sub into yours or call like any "sub"routine if you made it into a public sub and put into a Module. OF course then it only fires when you call it. e.g.

Sub Main()
    Sub1
    Sub2
End Sub

Sub Sub1()
    MsgBox "1"
End Sub

Sub Sub2()
    MsgBox "2"
End Sub

As shown in post #8 , after i=36, there would be no further changes to A1. The loop would continue to 1000.

I am not clear why you want to run calculate 1000 times. If you have a volatile function on the sheet and make changes with calculation mode as automatic, calculation event fires. Most people turn off calculation as I did so that speed is better.

I think explaining in more detail what you goals are might help us best help you. The calculation deal might be not be needed at all.

The worksheet is a machine learning system. The calculations are part of the learning process. The more calculations the smarter it gets.
Without going in great detail I use a rand function with just a few choices to set up a demographic for it to choose from. Now I want it to have to go through a history instead of having static choices.
I'm really bad with vba and I truly dont know how to put your vba into what I have.
Thanks again for your help