troubleshooting Question

VBA Worksheet Change Event to Copy Updating Info to Rows

Avatar of u002dag
u002dag asked on
Visual Basic ClassicMicrosoft ExcelVB Script
5 Comments1 Solution513 ViewsLast Modified:

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


Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros