VBA Worksheet Change Event to Copy Updating Info to Rows

u002dag
u002dag used Ask the Experts™
on

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


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In your Select Case you've repeated code (Range.Select and Selection.Copy) try to steer away from doing this to make your code more efficient.

Also, you don't need to copy to move values/formats
Range.Select
Selection.Copy

is the same as
Range.Copy

BUT:
you also have
Range2.Paste

you could cut all that out by
Range2.Value=Range.Value
Range2.NumberFormat=Range.NumberFormat

So have a look at the attached code and let me know if it works

p.s. your comments mention Target * 2 but there isn't any multiplication in the code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng 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
    
    Set rng = Range("A6:T6")
    If Target.Value >= 4 And Target.Value <= 6 Then
      rng.Offset(17 - Target.Value).Value = rng.Value
      rng.Offset(17 - 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

Open in new window

Author

Commented:
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

Author

Commented:
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

Author

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial