VBA Help

[Novice43]

Hello,

I have never used VBA and have been given a task to create an active button in an Excel worksheet that adds a new row to the end of the data set. It also needs to copy down any formatting/dropdown selections or formulas contained in the cells above. I am not sure if this makes any sense?

There is a form already in place that someone with more skills than me created so I have some coding but it does not seem to be working quite right. I am not going to lie this is like a whole new language to me and I am struggling. Any help much apricated .

This is the code I found:

Sub NewRecord()
On Error Resume Next
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.ShowAllData
Range("LACData[[#Headers],[Fwi]]").Select
Selection.End(xlDown).Select
Selection.Offset(1, 17).Select
ActiveCell.FormulaR1C1 = "No"
ActiveSheet.Protect , DrawingObjects:=False, _
Contents:=True, Scenarios:=False, _
AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=True, _
AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Application.ScreenUpdating = True

End Sub

 

[Logit]

.
See if this does what you need :

Option Explicit

Sub CopyPaste()

    ThisWorkbook.Worksheets("Sheet1").Activate
        With Cells(Rows.Count, 1).End(xlUp).Resize(1, 9)
        .Copy Destination:=.Offset(1, 0)
        End With
    Application.CutCopyMode = False
    
End Sub

 

[Novice43]

Hello,

Thank you so much this is great. I have been asked if there is anyway that it can only copy down the formula cells and conditional formatting? The are looking to have blank cells for all others to be manually populated. Apologies again if this is a silly question.

Also are there any site you would recommend that I could use to do some self development?

All help much appreciated.

 

[Logit]

You will need to explain your question in more detail. I don't understand the goal.

 

[Novice43]

So the cells under the White with black text columns need to be manually populated, The cells under the Purple with black text columns have drop down box's for selection, the Yellow with black text columns have formula's in them and the Blue with black text columns have conditional formatting.

What I am being asked to do is provide a click button that adds a new row at the bottom of the text that populates the Purple, Yellow and Blue cells with the Dropdown selection, Formulas and Conditional formatting but to leave the manual fields blank ready to be populated. Does this make any sense? I am very grateful for the time you are taking to help me.

 

[Logit]

Option Explicit

Sub Paste_Range()
Dim range_to_copy As Range, range_for_pasting As Range
Set range_to_copy = Range("A1:R1")      '<-- edit range to copy here

range_to_copy.Copy
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
End Sub