VBA- Making Log/History

pressure

New Member
Joined
Jun 9, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hi Guys!

I have a sheet that is going to be used every day and i want to press a button and cut today's data and copy in a history sheet. Im kinda new to VBA but was able to cut and paste the data of the day successfully but when i press the button to log another day, the data overwrite the last one, im not being able to paste it in the next blank row.

So i select A11, press Ctrl + Shift Right and Down to select all the data

.
1591746451104.png


And pasted it in the other sheet

1591746562861.png

How do i make it paste in the next blank space everytime?

My VBA looks like this right now

-------------------------------------------------------
Range("A11").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut

Sheets("Historico").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Gerenciamento").Select
Application.CutCopyMode = False
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Vathana

New Member
Joined
Jun 5, 2020
Messages
13
Office Version
  1. 2019
Platform
  1. Windows
You should avoid using Select unless you need it because it might cause unexpected error, e.g. you don't need to select a sheet/range to copy or paste value.
Also you should always specify of which sheet "Range" refers to.

Anyway can you try this,. Change "shDest" and "shSource" as your sheets.

VBA Code:
Sub CutRange()

    Dim r As Range, startRange As Range, destRange As Range
  
    'Set Destination Range = the next empty row of "A" on sheet "shDest"

    Set destRange = ThisWorkbook.Worksheets("shDest").Range("A" & Rows.Count).End(xlUp).Offset(1)
  
    With ThisWorkbook.Worksheets("shSource")

        'Set r = A11 to the last row/column of sheet "shSource" - Then cut it to destRange

        Set startRange = .Range("A11")
        Set r = .Range(startRange, startRange.End(xlDown).End(xlToRight))
        r.Cut destRange
      
    End With
  
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,008
Messages
5,639,516
Members
417,093
Latest member
Citrusandsage

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top