CODEWITHSUNDEEP
excelvbamacros
nahaelem
nahaelem

Reputation: 133

Excel Macro to copy row of data to bottom row

Good day,

I am trying to create a macro that copies data from the current month row to the bottom. The current month row is refreshed and the date will change for a new month (sep--16), but before it is refreshed, copy the data to the matching month. but i can't seem to get it to work.

enter image description here

    With test_example.Sheets("test")
    FinalRow = .Cells(rows.Count, 1).End(xlUp).Row
    'Loop through each row
    For x = 2 To FinalRow
       'Decide if to copy based on column B
       ThisValue = .Cells(1, 2).Value
       If ThisValue = "Aug--16" Then
           Sheets("sheet 1").Range("B2:G2").Copy
           lRow = .Range("A" & .rows.Count).End(xlUp).Row
           .Range("A" & lRow + 1, "Z" & lRow + 1).Select
        ActiveSheet.Paste
      End If
   Next x
 End With


   // edited code because i only have one sheet in the workbook
    Sub CopyData()
        FinalRow = Cells(Rows.Count, "C").End(xlUp).Row
        'Loop through each row
        For x = 2 To FinalRow
          'Decide if to copy based on column B
          ThisValue = Cells(1, 2).Value
          If ThisValue = "Aug--16" Then
            Sheets("Sheet1").Range("B2:G2").Copy
            lRow = Range("C" & Rows.Count).End(xlUp).Row
            Range("A" & lRow + 1, "Z" & lRow + 1).Select
            ActiveSheet.Paste
          End If
       Next x
    End Sub

Thanks for your help.

Upvotes: 2

Views: 8031

Answers (1)

mojo3340
mojo3340

Reputation: 549

Your code does not need to be so long, please try the below and let me know how it works for you :):

sub copydata()
Dim lrow As Long
lrow = Sheets("test").Cells(Rows.Count, 3).End(xlUp).Row
With Sheets("test")
    .Range("C2:G2").Copy
    .Range("C" & lrow + 1).PasteSpecial xlPasteValues
End With
end sub

Place a button on the worksheet, and press it before you create a new month :)

Upvotes: 1

Related Questions