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]

I can do this without much difficulty, but need to put it into context.

Describe a bit how you go about this manually, how you choose/decide what columns to paste into? Are there headers?

Are you happy for the macro to exist in the destination workbook?

Sure I could write code which will present you with each destination sheet, one at a time and ask you to select the destination - but it would be tiresome to do that for 50 sheets - there is very likely an easy way to let the code decide where to place stuff reliably on each sheet.

Later, we could use the text file directly - it may be easier and more reliable than you think! But that's for later - let's get something working first.

fyi, the sort of code might be something like this which (this code works on the one sheet) takes non-contiguous data from column A and places it in a differently patterned non-contiguous range in column F to match the non-contiguous pattern in column E:

'grab data for placing in multipledestinations:
Dim src()
X = 1
For Each cll In Range("A1:A20").SpecialCells(xlCellTypeConstants).Cells
  ReDim Preserve src(1 To X)
  src(X) = cll.Value
  X = X + 1
Next cll

'just one of those destinations:
Set dest = Range("F1:F20").Offset(, -1).SpecialCells(xlCellTypeConstants).Offset(, 1)
X = 1
For Each cll In dest.Cells
  cll.Value = src(X)
  X = X + 1
Next cll

This is not complete and will produce errors - but you can see where I tell it to get data from and that it's currently very rigidly defined, so this needs to be more complete and cater for variable amounts of data. You can also see that the destination is currently hard-coded, doesn't include sheet info, assumes that the destination starts at row 1, and this too needs to be made more flexible (for example, will it ever occur that data to be pasted can't go alongside existing data because there's too much data to paste?).

 

[StoneColdMarkyG]

Again you'll have to try and imagine the numbers in columns as my HTML isn't up to scratch.

Data already present (columns are alongside each other, the dashes are just to make the spacing work):

May----June----July
-4-------6-------3
-7-------9-------5

-3-------8-------7

-3-------2-------8

....and so on down the sheet with uniform blank rows across the sheet.

What I need to copy and paste in alongside, always 1 column to the right:

August
-5-

-55-
-52-

-2-

And so on, with non-aligned blank rows.

The macro would be fine in the existing workbook on the assumption that it would be flexible enough to adapt to different sheets having different existing spacing and the data being pasted in also not being uniform.

Thanks again for taking the time with this.

 

[p45cal]

You're still making me guess too much what's on the derstination sheets, so small steps at a time:
When you're pasting August's data:
1. Is there already a header in the destination column "August" or some such?
2. Does the data you're pasting include a header itself?
3. Are the headers on the destination sheets all in row 1?
4. Does the source data start in row 1? (If not, which row?)

Could you answer 1,2,3,4 above please.

If you wanted, and it would help a lot, could you place a sample of
a) the csv/txt file,
b) the excel source workbook, and
c) a destination workbook with say 3 destination sheets in with some earlier data already present

on the interweb somewhere (dropbox, box.net, skydrive, wherever)?