How to copy data from multiple cells of one part of my sheet to another?

Question

I have written a macro which copies values from one part of my sheet to another.

My goal is to repeat every X minutes to keep track of the history.

My code takes the value from cells C4, H4, H6, C3, H3, C5, H7, & H8 and pastes them in the next available cell in Column 15-22.

It throws

"Run-Time Error 9".. Subscript out of range

I think this means my loop is not working correctly.

Option Explicit

Dim RunTime As Date

Sub copy_nano()
'
' copy Macro
'
'
    RunTime = Now + TimeValue("00:02:00")
    Application.OnTime RunTime, "copy_nano"
    
    Worksheets("Nano Live").Range("C4", Range("C4")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("H4", Range("H4")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 16).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("H6", Range("H6")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("C3", Range("C3")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("H3", Range("H3")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("C5", Range("C5")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 20).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("H7", Range("H7")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 21).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        
    Worksheets("Nano Live").Range("H8", Range("H8")).copy
    Worksheets("Nano Live").Cells(Rows.Count, 22).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End Sub

It means the active workbook does not have a sheet "Nano Live". — GSerg, Mar 19 '21 at 13:52
Hi Both, thanks for the look. So I do have a sheet named "Nano Live" (Checked that there are no extra spaces etc...) @ gserg @BigBen I thought it would loop due to the Run Time function which essentially recalls the code every 2 Minutes in the above case. I guess thats not the right way to do it? Any recommendations on how to structure the "loop"? — Miner_of_metals, Mar 19 '21 at 13:59
The workbook in which this code resides may have a sheet called "Nano Live". The active workbook not necessarily contains it too. — GSerg, Mar 19 '21 at 14:01
Got it, thanks for that... Ill look at activating that exact sheet.. Could be due to the fact that I have about 7 excels open and am clicking between them. — Miner_of_metals, Mar 19 '21 at 14:26
`Ill look at activating that exact sheet` - [quite opposite](https://stackoverflow.com/q/10714251/11683), you should qualify your `Worksheets` with `ThisWorkbook`. — GSerg, Mar 19 '21 at 14:40
I found this question/answer helpful when I was first learning the basics of VBA https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Sobigen, Mar 19 '21 at 15:30
If the sheet named 'Nano Live' is in the active workbook use ActiveWorkbook.Sheets("Nano Live"), if it's in the same workbook as the code use ThisWorkbook.Sheets("Nano Live"). — norie, Mar 19 '21 at 16:50

score 0 · Answer 1 · answered Mar 23 '21 at 10:19

wanted to come back to you and A) Say Thanks for your direction/help. B) Say that I have found a solution which seems to be working. (See Below)

I didnt quite use a loop but it still works!!

Sub copy_nano()

' ' copy Macro ' ' Worksheets("Nano Live").Range("C4", Range("C4")).copy Worksheets("Nano Live").Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Worksheets("Nano Live").Range("H4", Range("H4")).copy
Worksheets("Nano Live").Cells(Rows.Count, 16).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("H6", Range("H6")).copy
Worksheets("Nano Live").Cells(Rows.Count, 17).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("C3", Range("C3")).copy
Worksheets("Nano Live").Cells(Rows.Count, 18).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("H3", Range("H3")).copy
Worksheets("Nano Live").Cells(Rows.Count, 19).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("C5", Range("C5")).copy
Worksheets("Nano Live").Cells(Rows.Count, 20).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("H7", Range("H7")).copy
Worksheets("Nano Live").Cells(Rows.Count, 21).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    
Worksheets("Nano Live").Range("H8", Range("H8")).copy
Worksheets("Nano Live").Cells(Rows.Count, 22).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Call test

End Sub

Sub test() Application.OnTime Now + TimeValue("00:10:00"), "copy_nano" End Sub

How to copy data from multiple cells of one part of my sheet to another?

1 Answers1