Can I get some help with this vba code to move a selected row to a specific sheet

[dpaton05]

I have a worksheet that allows you to transfer a row of a table to another sheet. I do have code that transfers rows based on date but sometimes there is a case where the rows will be required in a monthly sheet that is not of the month recorded on the date. For this case I was going to write code that allowed for a selection of a row then clicking on a button cmdCopySpecific, would transfer the row to the monthly sheet that has been specified by choosing a month from the combo box txtDirectMonth and typing the year into the textbox, txtDirectYear. The monthly sheets have the format in their names such as October 2018 etc.

Here is some code I have tried to write, copy and pasted from other places and it doesn't work. I don't know how to code and I wanted to get some help with code to do this.

It needs to also put formulas in the total columns on the destination sheet, L & M.

Here is a screenshot of the table https://www.screencast.com/t/cfohKVmXvm


Here is my code:

Private Sub cmdCopySpecific_Click()
'Worksheets("home").Unprotect Password:="costings"

txtDirectCombo.Value = txtDirectMonth.Value & " " & txtDirectYear.Value

    'turn screen updating off
    Application.ScreenUpdating = False
    
    'declare variables
    Dim lastrow As Long                                                                                     'number of first empty row in column A of Combo
    'Dim Combo As String                                                                                     'Combo worksheet name
    'Dim lasthomerow As Long
    Dim sht As Worksheet                                                                                    'number of first empty row in column A of All costings worksheet
    Dim lastcomborow As Long
    Dim Directcombo As Worksheet
    
    'assign values to variables
    Set Directcombo = Worksheets(txtDirectCombo)
    Set sht = Worksheets("home")
    Combo = sht.Range("Y5")
    lastrow = Worksheets(txtDirectCombo.Value).Cells(Rows.Count, "A").End(xlUp).Row + 1                                    'number of first empty row in column A of Combo
    lasthomerow = sht.ListObjects("tblCosting").Range.Rows.Count
    lastcomborow = Directcombo.Cells(Directcombo.Rows.Count, "A").End(xlUp).Row

With Selection
    .copy _
    Destination:=Directcombo.Cells(lastcomborow, 1)


        'Check if activities is selected in E5
        If Worksheets("home").Range("E5") = "Activities" Then
            Worksheets(txtDirectCombo.Value).Range("I" & lastrow).Formula = "=G" & lastrow                                 'copy formula to total column if activities are selected
            Worksheets(txtDirectCombo.Value).Range("H" & lastrow).ClearContents                                            'clear gst component if activities are selected
        Else
            Worksheets(txtDirectCombo.Value).Range("H" & lastrow).Formula = "=G" & lastrow & "*.1"                       'if activities are not selected, gst formula will be added in to column H
            Worksheets(txtDirectCombo.Value).Range("I" & lastrow).Formula = "=G" & lastrow & "+H" & lastrow               'if activities are not selected, add formula in to column I that will sum columns G + H
        End If
                                                                     

    'work with cell at intersection of LastRow and column A of Combo sheet
    With Worksheets(txtDirectCombo.Value).Cells(lastrow, 1)
        .PasteSpecial Paste:=xlPasteValues                                                                  'paste values
        .Columns("A").NumberFormat = "dd/mm/yyyy"                                                           'format date
        .HorizontalAlignment = xlLeft                                                                       'left align the date cell in column A
    End With

  

        
    Call SortDates                                                                                          'format cells to be in ascending date order

    Application.CutCopyMode = False                                                                         'cancel Cut or Copy mode

    Application.ScreenUpdating = True                                                                       'turn screen updating on
    
    Worksheets("home").Range("A5").Select
'Worksheets("home").Protect Password:="costings"

End Sub

I think I have included everything, let me know if I have missed something. Please help me with this code?

Thanks,
Dave

 

[igold]

Hi Dave,

Just doing a quick read of your code, and I stopped reading at this point. On this section of code where did you select anything to copy.

With Selection
    .Copy _
    Destination:=Directcombo.Cells(lastcomborow, 1)

 

[dpaton05]

Hi Dave,

Just doing a quick read of your code, and I stopped reading at this point. On this section of code where did you select anything to copy.

With Selection
    .Copy _
    Destination:=Directcombo.Cells(lastcomborow, 1)

I'm sorry, I didn't fully explain everything. I was thinking it might be easier for me to program. I was going to tell the user to select the row they want to move and then press the button to move the row but I am open to suggestions as to the best way to do this?