how do you offer yore logic to me and this is example. The data is being uploaded into my excel in pay something like that cells(1,1) = 200 cell(1,2) = alpa cells(2,1) = 250 cell(2,2) = veta cells(i,1) = ___ cell(i,2) = _____ ...............................
1
votes
1 Answers
0
votes
May try something like following
Public Var1, Var2, Var3, Var4
Private Sub Workbook_Open()
Dim Ws As Worksheet
Set Ws = ThisWorkbook.Sheets("Sheet1")
With Ws
'May use loop or Directly assigned to array if range is large
Var1 = .Range("B2").Value
Var2 = .Range("C2").Value
Var3 = .Range("D2").Value
Var4 = .Range("E2").Value
End With
End Sub
and at Sheet1
Calculate event
Private Sub Worksheet_Calculate()
Dim Cvar1, Cvar2, Cvar3, Cvar4
Dim LastRow As Long
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim ColNo As Long, ColLR As Long
With ThisWorkbook
Set Ws1 = .Worksheets("Sheet1")
Set Ws2 = .Worksheets("Sheet2")
End With
With Ws1
Cvar1 = .Range("B2").Value
Cvar2 = .Range("C2").Value
Cvar3 = .Range("D2").Value
Cvar4 = .Range("E2").Value
End With
If Cvar1 <> Var1 Or Cvar2 <> Var2 Or Cvar3 <> Var3 Or Cvar4 <> Var4 Then
With Ws2
LastRow = .Range("B" & Rows.Count).End(xlUp).Row + 1
.Range("B" & LastRow).Value = Cvar1
.Range("C" & LastRow).Value = Cvar2
.Range("D" & LastRow).Value = Cvar3
.Range("E" & LastRow).Value = Cvar4
.Range("A" & LastRow).Value = Now ' Used to log time in column A. May delete this line if not required
Var1 = Cvar1
Var2 = Cvar2
Var3 = Cvar3
Var4 = Cvar4
End With
End If
End Sub
modify the code to your requirement