asked on

VBA Worksheet Change Event to Copy Updating Info to Rows

Hello,
I'm importing external data from a web query. Updates every minute. Once a minute, I'm trying to copy one line of data to a (new) row of the worksheet so that I can store it and analyze it later for trends.
The key value that counts down, is in cell D4. It changes every minute and counts down from 24 to 1. Please help me to understand what I'm doing wrong.

Thanks,
Dennis

I tried to search all over the the web and the knowledge database.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "D4" Then

'Ensure target is a number before multiplying by 2
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 not loop
Application.EnableEvents = False
Select Case Target
Case Is = 6

Range("A6:T6").Select
Selection.Copy

Range("A11").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Case Is = 5

Range("A6:T6").Select
Selection.Copy

Range("A12").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Case Is = 4
Range("A6:T6").Select
Selection.Copy

Range("A13").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Case Else

Range("F1").Select

End Select

'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0

End If
End If

End Sub

ASKER CERTIFIED SOLUTION

bromy2004

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

u002dag

ASKER

Hi Bromy2004,
Thanks so much for the helpful tips to improve my efficiency and also for the code.
Unfortunately, when I add this VBA code and allow (or manually change the data in D4, nothing seems to happen.
I tried to reset the code, run Application.EnableEvents = True in the Immediate box, and a few other Debug attempts.

Attached, I manually created exactly what I'm looking for. I thought this might help you to help me.

Thanks so much for, what is for me, a complicated problem.

Thanks,
Dennis
First-response.xls

u002dag

ASKER

Bromy2004, One more thing,
I just changed the values from 1 to 24. Your offset value and rng.Value seem very logical.
Dennis

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

If Target.Address = "D4" Then
'Ensure target is a number
If IsNumeric(Target) Then

'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 not loop
Application.EnableEvents = False

Set rng = Range("A6:T6")

If Target.Value >= 1 And Target.Value <= 24 Then
rng.Offset(50 - Target.Value).Value = rng.Value
rng.Offset(50 - Target.Value).NumberFormat = rng.NumberFormat
Else
Range("F1").Select
End If

'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0

End If
End If

End Sub

u002dag

ASKER

Hello,
I still need help here.

Please excuse me for asking. Please see the attached file and the below code. Thanks So Much.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

If Target.Address = "D4" Then
'Ensure target is a number
If IsNumeric(Target) Then

'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target does not loop
Application.EnableEvents = False

Set rng = Range("A6:T6")

If Target.Value >= 1 And Target.Value <= 24 Then
rng.Offset(50 - Target.Value).Value = rng.Value
rng.Offset(50 - Target.Value).NumberFormat = rng.NumberFormat
Else
Range("F1").Select
End If

'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0

End If
End If

End Sub
First-response.xls

u002dag

ASKER

It didn't work exactly but you solution was helpful to give me new ideas and other avenues of approach for the problem. Thanks.