Copy Dynamic Range and Static Range and Paste to Another Range

[Nadine67]

Hello and thank you for any attention my post may receive.

I would like to copy static range ws1.Range("B4,C4,B6,B8") and a dynamic range with values (exclude all empty cells) within ws1.Range("J3:J21"), and paste to the next empty cell range in ws5.Range("C3:H1000"). So for every cell in ws1.Range("J3:J21") that meets the criteria I would like to paste it to ws5 column H and populate C:G with the static range.

I am able to paste the entire ws1.Range("J3:J21") to column H on ws5 with the following code, however that is not exactly what I need to do.

   ws1.Range("J3:J21").Copy
               ws5.Cells(Rows.Count, "H").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Any help will be greatly appreciated.

Thank you and have a great day!

 

[Peter_SSs]

Nadine

I'm struggling a little with your range sizes. You want to paste the static range into columns C:G (5 columns), but the static range only consists of 4 cells.

The code below (test in a copy of your workbook) should copy the static range into columns C:F and the dynamic range into column H. Post back if it needs modifications that you can't do yourself.

Dim wsAct As Worksheet
Dim aRws As Variant, StaticAry As Variant, DynamicAry As Variant
Dim rws As Long

Application.ScreenUpdating = False
Set wsAct = ActiveSheet
ws1.Activate
StaticAry = Application.Index(Range("B4,C4,B6,B8"), 1, 1, Array(1, 2, 3, 4))
aRws = Filter(Application.Transpose(Evaluate(Replace("if(len(#),row(#),""x"")", "#", "J3:J21"))), "x", False)
rws = (UBound(aRws)) + 1
DynamicAry = Application.Index(Cells, aRws, 10)     '<- 10 = column J
With ws5.Cells(Rows.Count, "H").End(xlUp).Offset(1)
  .Offset(, -5).Resize(rws, UBound(StaticAry)).Value = StaticAry
  .Resize(rws).Value = Application.Transpose(DynamicAry)
End With
wsAct.Activate
Application.ScreenUpdating = True

 

[Nadine67]

Oh my gosh Peter I do apologise for the confusion; I missed 'C6' from the static array. I do apologise for that.

Thank you for interpreting my request, even though it was a bit confusing, and developing this code. Peter I applied your code to my workbook, added the missing piece of the puzzle and executed it. I needed to make one small change and that was to change

DynamicAry = Application.Index(Cells, aRws, 10)

to

DynamicAry = Application.Index(Cells, aRws, 9)

. The result was exactly what I wanted.

Thank you Peter, you have made my day.

Have a great day!

 

[Nadine67]

***** solved *****

 

[Peter_SSs]

The result was exactly what I wanted.

Great news. Thanks for letting us know.