Can you please help with this Excel VBA code. I have data that updates once a minute. The following code works to copy a line of that data, every minute, and paste it on sequential rows for analysis later. There are two key values.
Data Set Number: There is one value copied to A3 that designates the data set. Data Set 1 provides 24 minutes of data, with minutes counting down from 24 to 0. Then A3 increments to 2 (data set two) and another 24 minutes of data is displayed on rows A6:AQ6. This continues with ten data sets.
Minute Number: The value in A4 counts down from 24 to 0 and that value moves the row down one at a time. Once Data Set 1 is done, Data Set 2 begins and A4 begins counting down from 24 to 0 again.
I am asking for Do Loops and/or other code to have Data Sheet One's 24 rows of data to be copied to Sheet 1, Data Sheet Two's 24 rows of data to be copied to Sheet 2, and so on. There are 10 sets of data per day, so 10 sheets per workbook. The attached file shows what it looks like on one sheet.
Critical: After the Data Sheet 1 is complete and the row of data corresponding to minute zero is entered, the code needs to detect that data from data sheet 2 is starting (by noting A3=2) and begin to copy the rows of data onto sheet 2. The code exits once the Data Set 10, Minute 0 is reached and copied.
By the way, the reason I'm using the Worksheet Calculate Function is because the data comes over the internet and the Worksheet Change Event doesn't detect the changing values.
I'm using this code one data set at a time. This project will help with efficiency. Future projects associated with this project will surely come. In approximately 1 month, I will have too much information and will need help with lookup functions in closed worksheets/workbooks.
Thank you in advance,
Private Sub Worksheet_Calculate()
'Declaring a variable called rng that will be used to capture the first row
Dim rng As Range
'A4 has a calculation in it and the worksheet is updating every minute
Set Target = Range("A4")
'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 not loop
[login to view URL] = False
Set rng = Range("A6:P6")
If [login to view URL] >= 0 And [login to view URL] <= 24 Then
Range("A6:P6").Copy Destination:=[login to view URL](35 - [login to view URL])
'Turn events back on
[login to view URL] = True
'Allow run time errors again
On Error GoTo 0