VBA- Making Log/History

pressure

New Member
Joined
Jun 9, 2020
Messages
1
Office Version
2019
Platform
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Vathana

New Member
Joined
Jun 5, 2020
Messages
13
Office Version
2019
Platform
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,102,820
Messages
5,489,058
Members
407,668
Latest member
MODELXLS

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top