Aligning pasted-in data to match blank rows

StoneColdMarkyG

New Member
Joined
Nov 10, 2011
Messages
6
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? :)
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Nov 10, 2011
Messages
6
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Nov 10, 2011
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Nov 10, 2011
Messages
6

ADVERTISEMENT

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.
 
Last edited:

p45cal

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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

New Member
Joined
Nov 10, 2011
Messages
6
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,969
Messages
5,599,104
Members
414,287
Latest member
Rbwester

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top