Copy/Paste non contiguous cells with same spacing

[Ebes1099]

Is it possible to Copy and Paste non-contiguous cells in a column with the same spacing in the destination? Right now, I can copy rows, 6, 8, 10, 12 but when I paste them, it pastes it into 4 consecutive rows.

I've got a column of data where I want to copy some cells as values and some as formulas. Instead of doing it in different bits of code where I do each subrange separately, I'd love to be able to say, Range("A10,A15,A17,A18").Copy and Paste it into rows 10, 15, 17, 18 of the next worksheet.

 

[SkipVought]

hi,

Can you do it manually, on the sheet?

That is your answer.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

I cannot do it manually on the sheet. But I would think there's a way in VBA. I can't run loops on the sheet manually, but I can do them in VBA, so that logic you propose doesn't make sense to me.

I think you could actually do something with a loop and store the row position to then paste the cells into the next workbook using that stored row position. But I haven't had time to think through that logic and try and create it, I wanted to check here first to see if anyone had this same experience and could offer their insight.

 

[SkipVought]

Looping is strictly programatic.

Copy 'n' paste is not.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

Yes, Skip, I understand that. But your logic doesn't necessarily hold true.

What I did for that was just copy and paste the ranges individually, kind of tedious but it works.

On something else though you can use a for loop like this and it works. I wanted to copy every other column and paste it into a new workbook with that same spacing. This worked specifically because it was every other on the same pattern. Without a pattern I'm not sure how you can do it.

For y = 0 To (numPlans * 2 - 1)
    Workbooks(Main).Activate
    Sheets("DocTbl").Select
    Range("GM49:GM51").Offset(0, y).Select
    Selection.Copy
    Workbooks(SuppDoc).Activate
    Sheets("Output").Select
    Range("C49").Offset(0, y).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    y = y + 1
Next y

 

[SkipVought]

For y = 0 To (numPlans * 2 - 1) step 2
    Workbooks(Main).Sheets("DocTbl").Range("GM49:GM51").Offset(0, y).Copy
    with Workbooks(SuppDoc).Sheets("Output").Range("C49").Offset(0, y)
       .PasteSpecial Paste:=xlPasteValues
       .PasteSpecial Paste:=xlPasteFormats
    end with
Next y

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}