VBA - Copy Values and Enter in next blank Row

[shemayisroel]

Hi,

Looking to enhance and tidy up the below code to use in a command button, I used the macro recorder to almost do what I need it to do.

What I'm trying to do in words is

- Copy the values in Range G19:V19
- Paste those values in Sheet "CommentsLog" starting from B2 but I need the code to find the next blank row to keep on pasting new values as required
- Once updated bring up a message box to advise user that the values have been updated and then clear the values in range G19:V19

Range("G19:V19").Select
    Selection.Copy
    Sheets("CommentsLog").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Plan vs Actual").Select
    Range("E19:F19").Select

 

[Sal Paradise]

You can use something like this to find the last row that's filled in:

intLastRow = .Range("A65535").End(xlUp).Row

Obviously this is just a snippet, but basically you can count up from the bottom of the sheet to get to the last filled row, and use an "Offset" or the like to put your data in the proper row.

 

[shemayisroel]

Thanks fo ryour input Sal.

I'm no VBA expert, and anything beyond some basics and the recorder I usually get stumped.

Not sure how to hang it all together...

 

[Trebor76]

Hi Sam,

I hope you're not working on a public holiday!!

If you're not interested in the original formatting, try this while on the tab with the figures to be moved:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long

    lngPasteRow = Sheets("CommentsLog").Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    With ActiveSheet
        Sheets("CommentsLog").Range("B" & lngPasteRow & ":Q" & lngPasteRow).Value = .Range("G19:V19").Value
        .Range("G19:V19").ClearContents
    End With
    
    MsgBox "The values in ""G19:V19"" have now been moved to Row " & lngPasteRow & " of Col B in the ""CommentsLog"".", vbInformation, "Move Actuals Editor"
    
End Sub

HTH

Robert

 

[shemayisroel]

Hi Sam,

I hope you're not working on a public holiday!!

If you're not interested in the original formatting, try this while on the tab with the figures to be moved:


HTH

Robert

Thanks Robert, working yes

What about you?

I'll give that code a try and let you know the result.

 

[shemayisroel]

I'll give that code a try and let you know the result.

Just tried it and a couple of changes (if possible), I shoukd have been a little more specific about my requirement.

1) In the 'Plan vs Actual' sheet cell V19 there is a formula, I need it put back in once the cells have been copuied over. The formula is =E3

2) There is not always going to be every cell populated in the range G19:V19, as such when looking fo rteh next blank row in the Sheet 'CommentsLog' it will need to be factored in as currently the way it works is it assumes there will always be data in G19 and if not over ride the values.

Make sense?

 

[Trebor76]

Hey Sam,

No, day off for me

See how this goes, now from any tab in the workbook:

Option Explicit
Sub Macro1()

    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False
    
    'If there's any data on the 'CommentsLog' tab, then...
    If WorksheetFunction.CountA(Sheets("CommentsLog").Cells) > 0 Then
        '...find the last row from columns B to Q (inclusive) and then increment it by 1
        lngPasteRow = Sheets("CommentsLog").Range("B:Q").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    'Else...
    Else
        '...manually set the variable to a default value of 2 (change to suit)
        lngPasteRow = 2
    End If
    
    With Sheets("Plan vs Actual")
        Sheets("CommentsLog").Range("B" & lngPasteRow & ":Q" & lngPasteRow).Value = .Range("G19:V19").Value
        .Range("G19:U19").ClearContents
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "The values in ""G19:V19"" have now been moved to Row " & lngPasteRow
    
End Sub

Cheers,

Robert

 

[shemayisroel]

Cheers Robert.

Off on another project now, I'll give this a go in the evening and report back any issues - otherwise assunme it's all ok and I thank you in advanced.

Thanks again for your help and speedy response.

 

[Trebor76]

You're welcome - any issues just let me know