a vba import into merged cell question

[John Caines]

Hello All.

I'm just trying to finish off a spreadsheet,, it's 95% done,,, but I really need help with 1 macro to finish it off, as it will be quite code intensive (For me anyway)

I would like to ask 1 question so I can clarify how the macro needs to be written.

My question is;
If for example I have a £ amount in cell K36 of £100, in a sheet called "Data Import"
And would like a macro button, that would export it to another sheet called Daily Targets and insert into Cell J24,,,, this can be easily achieved,,, I think so,, yes,,?

But,,,,,,,,,,,,,,,,
If J24 is a merged across cell,, IE is merged with K24,,, can this still be done in VBA?

Or,,, Does K36 have to be a merged across cell (As I could redo this sheet, so K36 is merged with L36,,,, so a merged cell is exporting to another merged cell?

I ask this because if you usually copy and paste cells into excel, and some are merged,, excel usually says via a pop up that the cell you are importing to is not the same size or shape,, (Or something to that effect)

If somebody could please just clarify this for me,, it would be most helpful,, so I know what I need to do/say regarding the new macro.


many thanks for all your time.

regards
john Caines

 

[VoG]

Hi John.

Merged cells are best avoided. It is better to format the cells horizontal alignment as Center across selection.

If you try to copy and paste it will probably fail. However, this should work

Sheets("Daily Targets").Range("J24").Value = Sheets("Data Import").Range("K36").Value

 

[John Caines]

Many thanks VOG for your reply,,,
i've just tried your suggestion,, but I couldn't get it to work

But,,, from what you have said,, merged cells are best avoided,, so I will redo my sheet before posting,, so I can explain correctly.

As a note,, the macro has to do several functions,,,but my 1st concern was the merged cell issue,, so on your advice I'll redo my sheet so it imports into only NON merged cells.

As a note VOG,, do you use excel 2010?
I didn't see it in your profile?
just wondered.

many thanks VOG,,
I'm redoing my sheet now,,,

All the best VOG
John Caines

 

[VoG]

Erik's code may help you http://www.mrexcel.com/forum/showthread.php?t=335123

Using 2010 at home 2000 at work

 

[John Caines]

Many thanks again VOG,,
I've managed to remove the merged cells,,,,
I just need to configure a few other bits,, then write a detailed post,, as the macro has to do several things,,, never simple with me !

Again,, many thanks VOG

Regards
JC