Copy calculated data ranges in Sheet2 O:O to Sheet1 offset column block areas in C:C

I apologize for being such a Noob but that's what I am and I need to do some advanced things with VBA and Excel. I look to this community as the helping experts and appreciate any help I get. Thanks for reading my post.

This seems like it would be an easy thing but I am struggling to get the desired results. I have searched the internet for days to solve this without finding the solution. I really need some help with this. But I have made some progress.

In Sheet2, I have a simple data stream of values coming in from a connection to a continually updating value table. Once the values have come they do not normally change.

In Sheet2 column "O:O" contains results from math performed on the incoming data table. This is simulated in the linked sheet. The basic result is a number with "0.000" format with the exception of two occasional text values. "Stop" and "Station". These text strings can be anywhere in O:O and are always the same text.

Each time a new value shows up in the table, the resulting values from the first calculation row in Column "O:O" down to and including the first text value need to be copied to a block range in Sheet1 starting at cell C21, named "Run_1_Start".

Once that has been done the next and subsequent calculated numeric values need to be copied to a new block range starting at cell C35, Named "Run_2_Start", until the second instance of text value. The process repeats with the each data sample copied to Next block range starting Cell.
Occasionally a block of data will spill over into the next so a function of looking at the next Sheet1 start cell in line to determine if it contains data then go to the next start cell would be beneficial. That can be handled later after the main goal is accomplished.

I can get and post the cell address of each text value in Sheet2 P:P adjacent to its O:O cell with the first code. Might be able to help define a range.
With the second code I can reliably paste the data without duplicates where it needs to go. I have manually assigned worksheet named ranges to the calculated data in Sheet2 which needs to be done automatically in VBA if it is to be used.

I have also manually assigned named cells at the start of each Sheet1 block to be pasted to which can remain static. When using a Named Range in Sheet1 i.e "Block1", caused the anomaly of if the copied data from Sheet2 was 7 cells, or half of the Sheet1 named range of 14 cells it would paste the same data twice, filling up the named range in Sheet1 with duplicates.

If I could use VBA to name the range in Sheet2, "Run_1" from the start, O2, to each new data result each time it comes in and is calculated to a value in O:O down to the occurrence of a Text value, Pasting the results to a named cell in Sheet1 "Run_1_Start" cell. Have the first Named range in Sheet2 remain from O2, to including the first text string, then start naming the next numeric value/s in Sheet2 O:O "Run_2" pasting that to down to and including the next occurrence of a Text value to Sheet1 "Run_2_Start" cell. This will eventually span a total of around 20 data blocks in Sheet1 so needs to be scalable

The addresses in Sheet2 P:P and all pasted data in Sheet1 C21:C can be erased to watch the macros I have work in the linked sheet bellow. Any Help would be greatly appreciated.

Thanks In Advance.

DataBlockCopyPasteExample.xlsm

https://drive.google.com/file/d/1ndF...ew?usp=sharing

Get and place in P:P the Cell Address of Stop or Station in O:O

Sub GetCellAddy()
    'Find all instances of "Stop" and "Station" in Sheet2 Column O:O then paste the cell name in adjacent cell in ColumnP:P
    'Stop Updating, Alerts, Events and Calcs for speed and fewer VBA Crashes
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    Worksheets(2).Select
row_count = 220  ' the number of rows in your range
For r = 1 To row_count
    If Cells(r, 15) = "Stop" Then
        Cells(r, 16) = Cells(r, 15).Address
    End If
Next r
For r = 1 To row_count
    If Cells(r, 15) = "Station" Then
        Cells(r, 16) = Cells(r, 15).Address
    End If
Next r
    'Restart Updating, Alerts, Events and Calcs after code completes
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Copy and Paste data appropriately but its dependent on manually named ranges and cells

Sub PasteNamedRange()
'This could work if I can write code to Name the range fron start to first "Stop" then fron the cell below first "Stop" to next "Stop" etc.


'Stop Updating, Alerts, Events and Calcs for speed and fewer VBA Crashes.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


      ' Run the Error handler "ErrHandler" when an error occurs.
      On Error GoTo Errhandler


Worksheets(2).Range("Run_1").Copy
Worksheets(1).Range("Run_1_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False 'Problem: when a range that is half of the Named Block size I paste to i.e.. Block1 on Sheet1, it pastes it twice.
Application.CutCopyMode = False
Worksheets(2).Range("Run_2").Copy
Worksheets(1).Range("Run_2_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Worksheets(2).Range("Run_3").Copy
Worksheets(1).Range("Run_3_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Worksheets(2).Range("Run_4").Copy
Worksheets(1).Range("Run_4_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Worksheets(2).Range("Run_5").Copy
Worksheets(1).Range("Run_5_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Worksheets(2).Range("Run_6").Copy
Worksheets(1).Range("Run_6_Start").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
    
'Put more copy lines here if I learn code to name ranges in Sheet2 based on the above search that works well


    'Restart Updating, Alerts, Events and Calcs after code completes.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


Worksheets(2).Select
Range("A1").Select
Application.CutCopyMode = False
Worksheets(1).Select
Range("A1").Select
Application.CutCopyMode = False


Errhandler:
    Worksheets(2).Select
    Range("A1").Select
    Application.CutCopyMode = False
    Worksheets(1).Select
    Range("A1").Select
    Application.CutCopyMode = False
        'Restart Updating, Alerts, Events and Calcs after code Error.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
        Exit Sub
        
                'Restart Updating, Alerts, Events and Calcs after code Error.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic


End Sub

Carim,
Thanks for that. This is a much more compact way to accomplish the task of identifying the cell address of the text entries. Great piece of work combining the search parameters. I know my application = False and application = True statements cloud up the code a bit.
The bigger problem for me is selecting the text cell and all the numeric cells above, repeating this with the second text entry excluding the first text entry and putting them both in the correct block on Sheet1.
I found these two bits of code that on my example workbook can copy and move the first block including the text within the same Sheet2 but I haven't yet discovered how to change the destination to Sheet1. I can easily play with the offset or range but nothing seems to work to change destination sheet. Everything I try gives an error so far. Maybe I will be able to combine disciplines. these both do the same thing in different ways but neither loop or search for all text values.

But seriously, Thank you for even looking at the problem I'm facing. You are the first response in 4 days of searching.

'Can't yet figure how to paste the results to a diifferent sheet
    Dim sht1 As Worksheet, r As Range
    Set sht1 = Sheets(1)
    Dim sht2 As Worksheet
    Set sht2 = Sheets(2)


    With sht2
        Set r = .Range(.Range("O2"), .Columns("O:O").Find("Stop", LookIn:=xlValues))
    End With
    r.Offset(0, 1) = r.Value

'Can't yet figure how to paste the results to a diifferent sheet
Dim sht1 As Worksheet
Dim sht2 As Worksheet, r As Range


Set sht1 = Sheets(1)
Set sht2 = Sheets(2)
Set r = sht2.Columns("O:O").Find("Stop", LookIn:=xlValues)


If Not r Is Nothing Then
    'should add sheet references here too
    With Range("O1").Resize(r.Row)
        Range("P1").Resize(r.Row).Value = .Value


    End With
End If

Carim,
I am not sure what kind of wizardry this is but it's brilliant. You indeed have made my life easier. I was close to shelving this for a while and redesign a new workbook that would run linear. This option with different blocks is far better. Works like a charm with all data that has a text value at the end of each block. I added more data to O:O and extended the Q:Q formula and had exactly the function I was hoping for. You fully ROCK and saved this project format for me.
This is so close to 100% for me now. I have code to erase the max known range names in Sheet2 based on number of blocks available in Sheet1 giving me the ability to make this a template for a new event. I can add an error handler to that code so if a "Run_X" range hasn't been created that macro won't hang.
There are only two things left to sort out.

1. Need the ability to dynamically paste each following numeric value from Sheet2 into the appropriate "Run_X_Start" cell on Sheet1 and below as they happen or at the click of a Button. (i.e.) If "Run_3" already exists and new Sheet2 O:O data is filling the soon to be "Run_4", These values need to be able to begin filling from "Run_4_Start" down until the next text value at which time they can be pasted over with the "Run_4" range data as it will be the same.
2. Need the ability to check if any "Run_X" block data has overflowed and pasted data to next "Run_Y_Start" cell, and if so paste the "Run_Y" range to the next "Run_Z_Start" cell. This is a rarer occurrence but can happen at times. Last priority for me. One way to handle this would be to expand the block size on Sheet1 so it is less likely.

You are my new VBA hero.

Carim,
You are indeed an OzMVP. I did not do my job well and didn't properly list the objectives on Sheet1 as I did in the OP or Post #5. I will try to clear that up as well.
I think I either broke the V3 or it doesn't work as hoped. I tried eliminating the 3rd Stop in O25, efectively making Run_3 = 17 cells to O28 to test the overrun data function and then hit the Process CMD Button. The "Run_3" data pasted into "Run_4_Start" cell as expected with one oddity. The data skipped the 14th cell in Run 3 block and the 14th - 17th pasted into the Run_4_Start block to get all 17 cells. This is not a big deal so that part is functional.

The bad news is that the "Run_4" data did not paste into "Run_5_Start" cell as desired, or anywhere else I can determine. Also after extending the Q formula and adding some new data to O:O, there was no copy of that to any location either.

I am playing with the number and scope of the Add Named Ranges feature. I found Multiple "Run_X" ranges in both Worksheet and Workbook scope. Maybe that is causing the trouble. I think these should be worksheet scoped and I think on my change of data that existed, all the previous named ranges should be deleted before adding new ones. I'll start by deleting all unnecessary, unused named cells and named ranges and macros. Then I'll add a call to a macro to delete all named ranges in Sheet2 at the beginning of the Add_NameRanges macro to see if that helps. I have a copy of V3 untouched to refer back to as well. I am playing with the potential of adding a Dynamic Named Range in VBA under the last named range in O:O as well. I can do this manually and that brings in the any new data but I'll study on creating it in VBA after the last Run_X range is added.Maybe an addition to your Q formula or a search for lastrow in Column Q and maybe an Offset to Column O and 1 row down to define Firstrow of the dynamic range.
I'm having trouble deciphering the CMD Button too. Noob thing. I'm used to Form Controls and Objects to attach macros to but not ActiveX Controls so much.
Thanks again for your help, I'm trying to learn some stuff. I'll keep you posted on any updates and new versions.

Carim,
Still working on the execution of this project. I have a way to create a Named Range for new data that works. Problem is the starting point is manual so I'm looking for code that would find the end of the last range or last q row value and start the named range row at the first numeric value after the last range. Maybe even a formula as you did to create the named ranges in order. Anyway I have tried several things with no joy thus far. It is so close that I can almost see it working.
Thanks Again for getting me this close. V4 Attached

Carim,
Sorry to confuse you. I think it is clear that I am not a VBA or Excel expert by any stretch of the imagination. I hope to clear this up for you by answering your questions in Post #10 first.
1. In this Scheme the "starting point" or cell in O:O for the "New_Data" Dynamic Range has to be able to find the end of the last Sheet2 named range, or start at O2 in the case of the start of the data stream session when there would be no named ranges yet, and in "Sub createNew_Data" change the "myFirstRow = X" line from my manual static input row of 38 now, to the cell after the last "Stop" or "Station" text occurred, wherever that may be in the process of Column O:O being filled with data results.
2. The final result being after the creation of any Sheet2 "Run_X" range would be the start point of the Dynamic Named Range of "New_Data" so it can be copied to the next open Sheet1 Run_X_Start cell, whichever that may be in Sheet1 C row. Then when Sheet2- "Run_Y" is created the "myFirstRow = X" value automatically starts after the last O:O text value. Also the "Sub GetNewData" has to have a dynamic ability to select the next open Sheet1, "Run_X_Start" cell as opposed to being directed to "Run_7_Start" manually as it is now.

3. Honestly I don't know if I need any named ranges or cells in Sheet1 or Sheet2. It is just the only way I could see to do it. As you know with VBA in Excel, there are many ways to make something work. The only thing I am certain of is the project goals.

Situation and end goal;
Lets say the Sheet2 column O:O could go down 200 rows, more or less is possible, filling each next row cell at the rate of one every 90.xxx seconds with a numeric value or the randomly placed text values as they occur in real time. Each time new results fill the next cell down O:O, it needs to be able to be placed in order in the correct "Run" block on Sheet1 rather than being all in one line as it comes from the stream. The run blocks on Sheet1 are currently 14 rows but this could be modified if needed. How, in VBA or Excel formulas or any combination of those, that end goal is accomplished makes no difference. I'm sure there is a better way that is cleaner and faster, I just don't know what it is.
I hope that clears up where I may have been confusing you. Enjoy your day.
Thanks for all your help so far on this.

Carim,
Sorry for the delay. Sadly V5 is fraught with some failures for me.
1. If you just change one of the Stops to a value making the run longer than 14 rows, while that range seems to be created properly it is skipped in the copy paste routine.
2. If you just start over with O:O data and make the 2nd run longer than 14 but less than 28 rows then hitting the Process button at each stop, the range is created but the long Run_2 doesn't paste.to blocks 2 and 3 as desired and is skipped.
3. regarding New_Data, as it should any New_Data still pastes to Run_7_Start because that's where it is pointed and the myFirstRow value had to be changed manually to match, again as expected.
I have been drawn off this for a fair while today. I did have the opportunity to try a different strategy altogether.this evening that works great with one big drawback. it worked like magic in my test workbook but failed in my Real World workbook. You will appreciate how lightweight and simple the really good the macro works. There are no named ranges or cells and it is super fast. it has one big problem to overcome that I have tried many things on to solve. It is documented on both Sheet1 and Sheet2. I have attached it for you as the code may help you out in the future. It is in failure mode right now but if you remove Sheet2, Cell O48 formula it is pretty amazing.
Thanks again. I owe you a beer, coffee, drink or ????

Carim,
This code seems to work like a charm all the way through. I deleted the current data and used IF statements in O:O as they would be on my Real World workbook and all seems to be fine as I fill in the O:O field with data in the way it will be done for the most part in the real world. The only thing that concerns me even a little is that when the Q:Q formula is extended down a number of rows,which it will have to be, it associates a number to all the empty rows resulting in a large number of one cell named ranges. I tried an IF statement at the beginning of your Q:Q formula that resulted in text cells being 1 so I removed that. It will work as it is. The extra named ranges are only a small concern for me because they are on a page I won't be using for display unless you think that naming 75-100 or more ranges early on would be taxing for Excel. The number of single cell ranges will go down as the real runs pile up.
Thanks again for your work on this and my education. I was involved heavily in a radio control forum some years ago where I had most of the answers for others, so I know how much time you likely spend helping others.
Like I said, I owe ya a Beer, Drink, Soda, Cup of Coffee or ??? for all you have done.

Carim,
Thanks for your work on this project of mine. I was involved with a radio control aircraft forum some years back in which I had most of the answers for people, so I know how much time you must be spending helping us stray dogs out. In my case a ton.
What we have is two Live action tests to run with data stream in the Real World workbook then with two of those workbooks running with the data streams connected to both.
Simulation with Real World workbook is up next!!

graha_karya,
Thanks for the input to my project. Always good to see more interesting code. The code you provided in Post 18 didn't do anything at all on my original workbook. please let me know if you were working on a different version.
The code below from Post 20 copies the first block of data from Sheet2 to Sheet1 then gives
Run-timeerror '1004':
Application-defined or object-defined error.

Quote from graha_karya

see it this if like post #19

Code

Sub tes1()
Dim r As Range, e As Range, U As Range, i&, j&, dic As Object, n&, v
Set dic = CreateObject("Scripting.dictionary")
With Sheet2
Set r = .Range("O2:O" & .Cells.SpecialCells(11).Row)
For i = 1 To r.Rows.Count
If IsNumeric(r(i, 1)) Then j = IIf(j = 0, i, j)
If ((r(i, 1) = "Stop") + (r(i, 1) = "Station")) Then
n = n + 1
Set dic("Run " & n) = .Range(r(j, 1), r(i, 1))
j = 0
End If
Next i
dic.Add "Run Last", .Range(r(j, 1), r(r.Rows.Count, 1))
End With
i = 21
With Sheet1
.Columns(3).ClearContents
For Each v In dic.keys
Set r = dic(v)
For Each e In r
.Cells(i, 3).Value = e   [SIZE=14px][I][B]'<<Errors Here.[/B][/I][/SIZE]
If e.Value = "Stop" Or e.Value = "Station" Then i = Cells(i, 1).End(xlDown).Row - 1
i = i + 1
Next e
Next v
End With
End Sub

Display More

Quote from Carim

Glad to hear you are about to see the finish line ... :wink:

Attached is your Version 7 .. with a tiny correction ...

Hope this will help ...:smile:

Well I see what you did here. I will test this one first as it seems to be much more responsive and lightweight in operation. I think I will still have to disable the 'Delete previous Named Ranges" code which seemed to work with V6.5 RC01

Thanks again!!

Carim,
I am in Proof testing of the Real world workbooks, two have to be running at the same time. So far so good for the code from V7.
There is one bug in one of the two workbooks that I am trying to fix. The second is a direct copy of the first, with some changes to code for workbook specifics and connected to a different data connection. In the case of the second workbook, in the target sheet, Sheet(1), Excel continually rewrites or changes some formulas with $A$2:$A$400 formulas that are pointed at Sheet(2) data sequentially to ref $A$X:$A$4XX depending on the last row that data was imported from. I haven't been able to determine if it is the data connection refresh or parameters. Both workbooks are identical in construction and somehow I have stopped the first workbook from doing this before I made the copy.
It has to have something to do with the data connection parameters on refresh which I have set to be manual only through a "GetData" Sub on both. The data connection is really the only difference between the two sheets.