Avatar of u002dag
u002dag
 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


Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
u002dag

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
bromy2004

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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.