Aligning pasted-in data to match blank rows

[StoneColdMarkyG]

Hi guys,

I'm tearing my hair out over this and hoping you can help.

I have a set of 50 or so large worksheets that need updating each month. My problem is the figures I need to update these sheets with are formatted differently (different alignment of blank rows) to the destination.

I'll try to illustrate, this is the sheet I need to add data to on a monthly basis... (you'll need to imagine the numbers in column form as I'm no HTML expert)

May June July
23 34 45
65 34 45
34 23 23

34 21 23
65 43 23
23 4 23
23 21 265
4 5 3

23 23 4

23 28 6
232 24 3

4 3 2

34 34 4

The blank rows need to remain uniform. When I need to paste in August's data the blank rows in the raw data extract are aligned differently and it's a laborious job deleting and inserting cells to get them to align (the sheet has hundreds of rows).

I'm capable enough of recording a macro to replicate the delting/inserting of cells BUT of the 50 or so sheets I need to update there's no uniformity of where the blank rows occur. To further exacerbate, there's no uniformity over where the blank rows occur in the data that needs to be pasted in, either. The blank rows already in the worksheets HAVE to remain and can't be deleted or altered.

So... anyone feeling particularly genius today?

 

[p45cal]

So you need to run down the column you're importing (August) and only grab non-empty values/cells, and paste them only next to cells which are not empty in July's column?

 

[StoneColdMarkyG]

So you need to run down the column you're importing (August) and only grab non-empty values/cells, and paste them only next to cells which are not empty in July's column?

That is correct (and far more succinct than I was!)

 

[p45cal]

Could you post some code that copies (incorrectly of course) the data across and I'll tweak it (or rather, I'll try to)?

 

[StoneColdMarkyG]

Could you post some code that copies (incorrectly of course) the data across and I'll tweak it (or rather, I'll try to)?

Unfortunately, there is no 'code' that does this. Sticking with the example of 'August's data', that will be produced by a third-party program where the output parameters can't be changed. I then have to copy and paste this into said 50 worksheets with their differently-spaced blank rows.

For historical consistency the sheets can't be reformatted to match the output from the third-party program.

 

[p45cal]

1. What form does August's data take? we'll get the macro to use that directly.
2. How do you decide in which column to put th data?

 

[StoneColdMarkyG]

1. By the time it gets to me it's in XL format - just one spreadsheet to another. It's literally just copying one column of figures alongside another and making the blank rows align. But where those blank rows appear is not consistent. The amount of variables is though, if that helps.

It begins as a CSV file but I don't think we need to complicate things further!

2. The 'new' data is always pasted 1 column to the right of the existing data.

 

[p45cal]

So it comes as a separate worksheet (workbook?) with the data in column A and you need to copy that column to columns(answer to Q2 in msg#6) into 50 sheets in the workbook the code will be in, each with its own pattern of rows? Are the 50 sheets the only sheets in the workbook, or are there sheets in the same workbook that data should not be copied to?

 

[p45cal]

I need to figure a way to find which column is the destination column in each destination sheet - would the top row (headers?) be a reliable guide to find that column? If not, the row beneath - is it always NOT a blank row?

 

[StoneColdMarkyG]

The 50 worksheets that need updating are all within the same workbook. The first two sheets of this workbook are NOT upated monthly (Notes and Contents). The 'new' data comes from a separate workbook.

And, unfortunately, there's no consistency to what the destination column would be.

Thanks so much for your time and patience with this.