Copy/Paste with blank cells inbetween

[MartinLiss]

I have a worksheet where B3 is "1", B4 is "5", B5 is "7" etcetera to B8. I'd like to paste those values to another worksheet starting in C5 but with a blank cell in-between the output cells. In other words D5 would be "1", D6 would be blank, D7 would be "5", etcetera, and I'd like to do it without a link and instead perhaps use Transpose or Join.

BTW WHAT HAS HAPPENED TO THE FORMATTING TAGS FOR THESE POSTS?

[jdelano]

You could use a for loop, it would look something like:

    Dim fromRow as Integer
    Dim toRow as Integer

    Dim fromWS as Worksheet
    Dim toWS as Worksheet

    Set fromWS = ThisWorkbook.Sheets("Sheet1")
    Set toWS = ThisWorkbook.Sheets("Sheet2")

    toRow = 5
    For fromRow = 3 to 8
        tows. Cells(toRow, "D").Value = fromWS.Cells(fromRow, "B").Value
        toRow = toRow + 2
    Next fromRow

    Set fromWS = Nothing
    Set toWS = Nothing

[MartinLiss]

Thanks but I know how to use a loop, and what I was hoping for was a way to do it without one.

[Zvoni]

If you mean with a Formula, the only thing coming close to your requirement was this:
https://www.reddit.com/r/excel/comme...ank/?rdt=54620

Note: This is for blank Columns.
Will have to be reworked for Rows (if it's even possible)

[MartinLiss]

Zvoni, I am looking to insert blank columns, and the formula in your reference looks promising, but despite what my question said, the number of values in the source is variable and there could be a hundred or more values in it, so I assume I would need to use VBA to populate the formula across the destination row. Am I correct?

[Zvoni]

You're right: In your first post there is no mention of blank columns to insert, only "Transpose" hinting at it.

That said: I'd need to see a "This is what i have, and this is what it should look like"
because, frankly: I'm struggling imagining the end result

[MartinLiss]

Here is a link to a demo file. When you click the button two new worksheets will be created from the Database worksheet's data.

https://www.mediafire.com/file/4g5ry...Demo.xlsm/file

[Zvoni]

Sorry, Martin.

Company-Laptop here. No Access to filesharing services, nevermind xlsm-file

Would prefer a Screenshot (if the current Forum-Issues allow that)

[MartinLiss]

The forum is not even allowing me to add a picture so this is the best I can do.
SOURCE
Column A: a date
Column B: 1
Column C: 2
Column D: 3
Column E: 4
Column F: 5
Column G: 6
Column H: 7

DESIRED OUTPUT
Column D: 1
Column E: blank
Column F: 2
Column G: blank
Column H 3
Column I: blank
Column J: 4
Column K: blank
Column L: 5
Column M: blank
Column N: 6
Column O: blank
Column P: 7

[Zvoni]

hmm.... i take it, the source is all in one row across the mentioned columns, and the Destination is on a different worksheet/some rows below it?

[MartinLiss]

Yes.

[Zvoni]

I think i have it

I took your description above, and filled an Excel sheet with your sample Data.
Starting in Column B1, and continuing through C1, D1 etc.... i entere: 1,2,3,4,5,6,7 (all in Row 1)

I then entered following Formula in D4:
=IF(MOD(COLUMN(B1),2)=1,"",OFFSET($B$1,,ROUNDUP(COLUMN(B1)/2,0)-1))

and "copy-dragged" it to the right
Value in D4 is 1, in E4 is blank, in F4 is 2, G4 is blank...etc.

Important: Look closely at relative/absolute Addressing in the Formula

[Zvoni]

OK, tested it in a different sheet, too.
Just add the Sheet-Qualifier to the Addresses ("Sheet1!B1" etc.)

[Zvoni]

uh.... very nice feature of the Forum now: If you go on Edit (to edit your post), your "original" Entry vanishes......

[MartinLiss]

BTW, I know how to use Join on the source data to produce a string like 1-2-3-4-5-6-7 where the dashes represent spaces, but I could not figure out how to paste that string into the output row formatted like
Column D: 1
Column E: blank
Column F: 2
Column G: blank
Column H 3
Column I: blank
Column J: 4
Column K: blank
Column L: 5
Column M: blank
Column N: 6
Column O: blank
Column P: 7
in just one statement.

[Zvoni]

See my Description/Formula in Post 12
Tested in the same sheet as well as in a different sheet (albeit with Sheet-Qualifier in the Cell-Address)

[MartinLiss]

Thanks, but as i mentioned above "...the number of values in the source is variable and there could be a hundred or more values in it, so I assume I would need to use VBA to populate the formula across the destination row. Am I correct?"

[Zvoni]

In that case i?d say yes, since i?m not aware Excel being capable to determine last column/row during runtim to adjust/propagate formulas forward

[BalNoinc]

Have you considered using a dynamic VBA solution to populate the formula across the destination row, given the variable number of values in the source data?