VBA Copy & Paste to last row

[MikeWall]

Hi guys,<o></o>
I wouldlike to create a copy and paste to last row macro on excel.<o></o>
<o> </o>
In my excelfile, sheet 1 is called table. Sheet two is called rolling table which will be aconsolidation of past tables.<o></o>
The tableswill have the same widths so from A2:BP2, whilst the length varies. I am tryingto use a lastrow function in excel but it’s not working.<o></o>
The aim offunction is to prevent operational risk. More added functionality is the macrodeleting a row based on the date for that row. I have to think this more carefully.<o></o>
<o> </o>
For nowthis is the code I have.<o></o>
<o> </o>
Much helpwould be appreciated<o></o>
<o> </o>
Sub RollingTable()<o></o>
' RollingTableMacro<o></o>
Dim Last_Row As Long<o></o>
Sheets("VA").Activate<o></o>
Last_Row = Range("A" &Rows.Count).End(xlUp).Row<o></o>
Range("B2:BP2").CopyRange("B2:BP" & Last_Row)<o></o>
Range(Selection,Selection.End(xlToRight)).Select<o></o>
Range(Selection,Selection.End(xlDown)).Select<o></o>
Selection.Copy<o></o>
Sheets("RollingTable").Select<o></o>
Last_Row = Range("A" &Rows.Count).End(xlUp).Row<o></o>
Range("B2:BP2").CopyRange("B2:BP" & Last_Row) <o></o>
End Sub<o></o>
<o> </o>

 

[baitmaster]

I use this function in pretty much every workbook I create

Function lastUsedRow(ws As Worksheet) As Long
On Error Resume Next
    lastUsedRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0
End Function

You call it using e.g.

lastrow = lastusedrow(Sheet1)

and it tells you the actual last used row on a worksheet. Not the last cell of the usedrange, and not the last cell of a specific column. For the next empty row simply add 1

In your case, I'm guessing the issue is that you're looking at column A, which probably contains not data. Try using Last_Row = Range("B" &Rows.Count).End(xlUp).Row instead

I also don't recognise the term CopyRange, and pretty much all of your select/.selection is unnecessary, so you should be able to get rid of most of it

Based on this, I can't quite tell what your code is trying to do. The following is an example that takes the last row of the VA worksheet and adds it to the next empty row of the RT worksheet. Hopefully this should at least point you in the right direction:

Sub RollingTable()

Dim lastRowVA As Long
lastRowVA = lastUsedRow(Sheets("VA"))

Dim lastRowRT As Long
lastRowRT = lastUsedRow(Sheets("RollingTable"))

Sheets("VA").Rows(lastRowVA).Copy
Sheets("RollingTable").Rows(lastRowRT + 1).PasteSpecial xlPasteAll

End Sub

Function lastUsedRow(ws As Worksheet) As Long
On Error Resume Next
    lastUsedRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0
End Function

 

[MikeWall]

Hi Baitmaster G,

You're spot on, I really don't need copy/range or selection but I started attempting to build this macro by recording myself doing this procedure.

Thanks for the response. I just ran the code there and it seems only to be copying the last row in VA & pasting into table. Rather than copying from A2 to lastrow.

Thanks again,


MikeWall

 

[baitmaster]

So are you saying take everything in the VA table from rows 2 to lastRowVA, and appending the whole lot onto the end of the RT table?

Sub RollingTable()

Dim lastRowVA As Long
lastRowVA = lastUsedRow(Sheets("VA"))

Dim lastRowRT As Long
lastRowRT = lastUsedRow(Sheets("RollingTable"))

Sheets("VA").Rows([COLOR=#FF0000]"2:" & [/COLOR]lastRowVA).Copy
Sheets("RollingTable").Rows(lastRowRT + 1).PasteSpecial xlPasteAll

End Sub

Function lastUsedRow(ws As Worksheet) As Long
On Error Resume Next
    lastUsedRow = ws.Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row
On Error GoTo 0
End Function

 

[MikeWall]

Hi Baitmaster G,

You're spot on, I really don't need copy/range or selection but I started attempting to build this macro by recording myself doing this procedure.

Thanks for the response. I just ran the code there and it seems only to be copying the last row in VA & pasting into table. Rather than copying from A2 to lastrow.

Thanks again,


MikeWall

I replaced this 'Sheets("VA").Rows(lastRowVA).Copy with - Range("A2:BP" & lastRowVA).Copy and it works.

Is this what you would have done ?

Thanks again

 

[baitmaster]

If it works it works

I'm particularly pedantic and like to completely automate my code, even in the event that the excel sheets are changed - e.g. a row or column is added somewhere, or something is moved. If this happens, then your code will fail because the following terms are hard-coded: A, 2 & BP

To avoid this I would probably have used a named range within my tables, and then referred to row X of that named range. If the table itself is moved then the code should still work

Being really pedantic as I am, I'd probably also write a test that ensures both tables (source and destination) are the same width, stuff like that. Good coding needs to try and think of everything that could be reasonably expected to go wrong and ensure it doesn't destroy your data because something moved. You can't catch everything, but data moves easily and I've been burnt too many times! Named ranges should be second nature, use them every time you code. Maybe you won't do this yet... I guarantee you will

 

[MikeWall]

If it works it works

I'm particularly pedantic and like to completely automate my code, even in the event that the excel sheets are changed - e.g. a row or column is added somewhere, or something is moved. If this happens, then your code will fail because the following terms are hard-coded: A, 2 & BP

To avoid this I would probably have used a named range within my tables, and then referred to row X of that named range. If the table itself is moved then the code should still work

Being really pedantic as I am, I'd probably also write a test that ensures both tables (source and destination) are the same width, stuff like that. Good coding needs to try and think of everything that could be reasonably expected to go wrong and ensure it doesn't destroy your data because something moved. You can't catch everything, but data moves easily and I've been burnt too many times! Named ranges should be second nature, use them every time you code. Maybe you won't do this yet... I guarantee you will

I probably should have used name ranges. I'm new to VBA/Coding so probably should start off using the right approach.

Speaking things that could go wrong, I'm think of adding a check for dupes.so lets days I clicked a button and the macro saves the file once. However I click the button again and the same data will be saved, but if there's no new data to be copied what's already copied in the 2nd instance are just dupes.

So I want to add a function that would filter out or removed duplicate values based on a criteria. I'm thinking for loops or conditional formatting in excel.

 

[baitmaster]

You could of course loop through your data and delete rows one at a time. If you do this, ALWAYS start at the bottom and work up, because deleting one row moves everything below it so your loop will become out of sync if you start at the top

I have a simple piece of code that removes any entire row where a single cell in my one-column selection contains a TRUE (/FALSE) statement - so you can write any TRUE/FALSE question, then delete the row where it is met

Sub removeTRUE()
remove ("True")
End Sub

Sub removeFALSE()
remove ("False")
End Sub

Sub remove(strBoolean As String)
' removes entire line where TRUE or FALSE is found in active column
Dim rngTest As Range: Set rngTest = Selection
Dim i As Long

If rngTest.Columns.Count <> 1 Then
    MsgBox "please select only one column for this macro"
    Exit Sub
End If

Application.calculation = xlCalculationManual

For i = rngTest.Rows.Count To 1 Step -1
    With rngTest.Cells(i, 1)
        If .Value = "True" Then .EntireRow.Delete shift:=xlUp
    End With
Next i
       
Application.calculation = xlCalculationAutomatic
       
End Sub

I don't use it much any more because it's slow, but it still has it's uses. In your case you might investigate a coded version of the Data > Remove Duplicates feature which would be much quicker