CODEWITHSUNDEEP
vbaexcelcopy-pastenamed-ranges
Henrik Hultén-Karlshøj
Henrik Hultén-Karlshøj

Reputation: 3

Copy a range from sheet1 to sheet2

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

  1. I have a range ("A4:C500") in Sheet1 that has some empty cells from eg. row 180
  2. I would like to copy that entire range to Sheet2
  3. 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)
  4. 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

Upvotes: 0

Views: 805

Answers (1)

Miguel_Ryu
Miguel_Ryu

Reputation: 1418

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:

Upvotes: 1

Related Questions