VBA that copies paste to the top of a worksheet instead of bottom

[CBear99]

I am using the below code to update values every 10 minutes. The macro counts from the bottom of the worksheet and adds the latest value to the blank cell from the bottom up. So basically it is continuously adding values to the next open cell from the bottom which extends the range

Question:

Is there a way to do the reverse? That is, insert a row at the top of the worksheet (create an empty row) and then add the latest value there? The intent is to eventually create a rolling chart because the amount of data that I will be adding will quickly exceed the chart range if the values are continuously being added to the bottom of the worksheet and not the top.

Any help or guidance would be greatly appreciated

Thanks,

-CBear

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
' Select Sheet1 cells A1 and B1 and copy
Worksheets("Sheet1").Range("A1:B1").Copy
' Find the next empty row based on column A
Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Select
' Paste the copied data
ActiveSheet.Paste


' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

Sub TimerReset()
' Instructs Excel to run "Copy_A1B1" macro again in 15 minutes
Application.OnTime Now + TimeValue("00:10:00"), "Copy_A1B1"
End Sub

 

[offthelip]

I have assumed that Sheet1 is the active sheet because otherwise your existing code is a bit strange
try this:

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

 

[CBear99]

I have assumed that Sheet1 is the active sheet because otherwise your existing code is a bit strange
try this:

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

Sweet that worked very well! Now I would like to apply this code to several columns at the same time not just column "A"

I was able to get it work for column "B" but was unable to get to work for column "C"

Not sure what to change in the below code in order for it to be able to apply it to additional columns?

Any Ideas?

Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr
Application.ScreenUpdating = True

lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr
Application.ScreenUpdating = True
____________

[The below code did not work for some reason}

lastrow = Cells(Rows.Count, "C").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr
Application.ScreenUpdating = True

 

[offthelip]

I have annotated my code with an explanation of what each line is doing :

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this line just detects the last row in column a with a value in it
inarr = Range(Cells(1, 1), Cells(lastrow, 2)) ' this loads rows 1 to last from column 1 and 2 into a variant array  ( inarr)
                  'note the second number in each of the cells(n,n) is the column number
                  '  column 2 is column B
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr ' the writes the variant array back to the workhkseet into rows 2 to lastrow +1 and column 1 and 2 again
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

You will see that your code in red doesn't work becaus all it is doing is detecting the last row in column C and then copying columns A and B again
I had assumed that the lastrow in A is also the position of the last row in B , Is this the case?
When you extend it if the last row in C is the same as the last row in A you only need to detect it once if this is the case then to include column C you need to change the array to pick up coluimn C and also to write it back e.g. like this:

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this line just detects the last row in column a with a value in it
inarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' this loads rows 1 to last from column 1 and 2 into a variant array  ( inarr)
                  'note the second number in each of the cells(n,n) is the column number
                  '  column 2 is column B
Range(Cells(2, 1), Cells(lastrow + 1, 3)) = inarr ' the writes the variant array back to the workhkseet into rows 2 to lastrow +1 and column 1 and 2 again
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

If column B and C lastrow is different then you need to repeat the code for each column

 

[CBear99]

I have annotated my code with an explanation of what each line is doing :

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this line just detects the last row in column a with a value in it
inarr = Range(Cells(1, 1), Cells(lastrow, 2)) ' this loads rows 1 to last from column 1 and 2 into a variant array  ( inarr)
                  'note the second number in each of the cells(n,n) is the column number
                  '  column 2 is column B
Range(Cells(2, 1), Cells(lastrow + 1, 2)) = inarr ' the writes the variant array back to the workhkseet into rows 2 to lastrow +1 and column 1 and 2 again
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

You will see that your code in red doesn't work becaus all it is doing is detecting the last row in column C and then copying columns A and B again
I had assumed that the lastrow in A is also the position of the last row in B , Is this the case?
When you extend it if the last row in C is the same as the last row in A you only need to detect it once if this is the case then to include column C you need to change the array to pick up coluimn C and also to write it back e.g. like this:

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this line just detects the last row in column a with a value in it
inarr = Range(Cells(1, 1), Cells(lastrow, 3)) ' this loads rows 1 to last from column 1 and 2 into a variant array  ( inarr)
                  'note the second number in each of the cells(n,n) is the column number
                  '  column 2 is column B
Range(Cells(2, 1), Cells(lastrow + 1, 3)) = inarr ' the writes the variant array back to the workhkseet into rows 2 to lastrow +1 and column 1 and 2 again
Application.ScreenUpdating = True
' Call the "TimerReset" macro
Call TimerReset
' Save the workbook
ActiveWorkbook.Save
End Sub

If column B and C lastrow is different then you need to repeat the code for each column

Wow that worked Perfect! One last simple question. I just need code that can stop the macro from running?

 

[offthelip]

The way I deal with this sort of situation is have a manually controlled switch off and an automatic switch off.
This logic checks to see if less than 1000 rows exist and also checks that cell G3 is true. So you need to set G3 to true when you start the recording and you cna manually set if false to stop it.

Sub Copy_A1B1()
' Sets screen updating to false to prevent screen flicker
Application.ScreenUpdating = False
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row ' this line just detects the last row in column a with a value in it
inarr = Range(Cells(1, 1), Cells(Lastrow, 3)) ' this loads rows 1 to last from column 1 and 2 into a variant array  ( inarr)
                  'note the second number in each of the cells(n,n) is the column number
                  '  column 2 is column B
Range(Cells(2, 1), Cells(Lastrow + 1, 3)) = inarr ' the writes the variant array back to the workhkseet into rows 2 to lastrow +1 and column 1 and 2 again
Application.ScreenUpdating = True
' Call the "TimerReset" macro
' add some logic around reset
If Lastrow < 1000 And Range("G3") = True Then
Call TimerReset
End If
' Save the workbook
ActiveWorkbook.Save
End Sub