Copy a range from sheet1 to sheet2

Question

I'm pretty new to VBA, but I hope someone can help me. Here is what I would like to do;

I have a range ("A4:C500") in Sheet1 that has some empty cells from eg. row 180
I would like to copy that entire range to Sheet2
Afterwards, if I insert a new row in Sheet 1 in eg. "A24:C24" and I fill eg "A24:C24" with values, I want to first insert an entire new row in "A24" in Sheet2 (same row as the new row in Sheet1) and move all existing cells 1 row down in Sheet2. The new row in Sheet2 should copy formulas from the row above (eg. row 24 should copy formulas from row 23)
After the new row has been inserted in Sheet2, I would then like to copy values from Sheet1 "A24:C24" to Sheet2 "A24:C24"

Regarding 2. I have tried using some code like;

Sub Copy()
    Ark1.Range("A4:C500").Copy
    Ark2.Range("A4").Rows("1:1").Insert Shift:=xlDown
End Sub

and that basically takes care of 2.

How can I get 3. and 4. to work? Any ideas for me to try out?

Thanks in advance

You know this has been asked a couple hundred times this week alone. Did you even attempt to search for your answer? https://stackoverflow.com/questions/38024967/excel-vba-copy-range-from-one-sheet-to-another-next-empty-row?s=1|179.2438 / https://stackoverflow.com/questions/46771730/vba-copy-variable-range-from-one-sheet-to-another?s=2|160.6739 / https://stackoverflow.com/questions/21862851/copy-range-from-one-sheet-paste-part-of-range-in-same-sheet-based-on-cell-value?s=4|127.1215 — K.Dᴀᴠɪs, Jan 31 '18 at 21:27
Possible duplicate of [VBA EXCEL copy range](https://stackoverflow.com/questions/28730624/vba-excel-copy-range) — K.Dᴀᴠɪs, Jan 31 '18 at 21:32
Consider adding more tags to you question, this should help refresh the list of already answered questions similar to what you are after. — Gros Lalo, Jan 31 '18 at 21:33

score 1 · Accepted Answer · answered Feb 01 '18 at 10:25

To do 3 and 4 you can use the code below:

With Ark1
    'insert new row in sheet one
    .Row("24").Insert Shift:=xlDown
    'fill range A24:C24 with values
    .Range("A24:C24").Value = Array("Your", "Values", "Here")
    ' Or if specific cell values (.cells(row, column).value) Array(.cells(1,1).value, .cells(1,2).value, .cells(1,3).value)
End With

With Ark2
    'copy row to retain formulas
    .Row(23).Copy
    'insert new row in sheet two
    .Row(24).Insert Shift:=xlDown
    'get values from sheet one to sheet 2
    .Range("A24:C24").Value = Ark1.Range("A24:C24").Value
End With

P.S.: As K.Dᴀᴠɪs mentions in his comments there is a lot of documentation to help you out with issues such as these. K.Dᴀᴠɪs provided links:

Copy a range from sheet1 to sheet2

1 Answers1

Linked