Copying ranges

Hi,

Scenario:
2 Workbooks with identical worksheets i.e. Same layout, same controls.
The main difference is that Workbook 1 one has data. Workbook 2 doesn't.

Problem
How do I copy a range of data from workbook 1 to workbook 2, including cell
formatting, without also copying the controls across.

I've tried a straight range copy but I finish up with duplicate controls.

If I try copying the values, I get a merged cells complaint (although what
few merged cells there are the same in each case) - the formatting is
required anyway.

I can do it if the properties of the controls in workbook 1 are set to
"don't move or size with cells" but it's not really practical.

Thanks,
Don

Hi Don,

Try a dual copy / PasteSpecial operation: firstly values and then formats or
vice versa).

---
Regards,
Norman



"Don Lloyd" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Scenario:
> 2 Workbooks with identical worksheets i.e. Same layout, same controls.
> The main difference is that Workbook 1 one has data. Workbook 2 doesn't.
>
> Problem
> How do I copy a range of data from workbook 1 to workbook 2, including
> cell formatting, without also copying the controls across.
>
> I've tried a straight range copy but I finish up with duplicate controls.
>
> If I try copying the values, I get a merged cells complaint (although what
> few merged cells there are the same in each case) - the formatting is
> required anyway.
>
> I can do it if the properties of the controls in workbook 1 are set to
> "don't move or size with cells" but it's not really practical.
>
> Thanks,
> Don
>

Thanks Norman,

I've tried that but I get a merged cells complaint (although what
few merged cells there are the same in each case) when I paste the values.
I hate merged cells in VBA and don't use them often :-)

Don

"Norman Jones" <[email protected]> wrote in message
news:u%[email protected]...
> Hi Don,
>
> Try a dual copy / PasteSpecial operation: firstly values and then formats
> or vice versa).
>
> ---
> Regards,
> Norman
>
>
>
> "Don Lloyd" <[email protected]> wrote in message
> news:[email protected]...
>> Hi,
>>
>> Scenario:
>> 2 Workbooks with identical worksheets i.e. Same layout, same controls.
>> The main difference is that Workbook 1 one has data. Workbook 2 doesn't.
>>
>> Problem
>> How do I copy a range of data from workbook 1 to workbook 2, including
>> cell formatting, without also copying the controls across.
>>
>> I've tried a straight range copy but I finish up with duplicate controls.
>>
>> If I try copying the values, I get a merged cells complaint (although
>> what few merged cells there are the same in each case) - the formatting
>> is required anyway.
>>
>> I can do it if the properties of the controls in workbook 1 are set to
>> "don't move or size with cells" but it's not really practical.
>>
>> Thanks,
>> Don
>>
>
>

Hi Don,

> I hate merged cells in VBA and don't use them often :-)

I never use them.

>>> I can do it if the properties of the controls in workbook 1 are set to
>>> "don't move or size with cells" but it's not really practical.

Why not write a simple macro to loop through the controls setting and then
(after the copy operation) un-setting the property?

---
Regards,
Norman



"Don Lloyd" <[email protected]> wrote in message
news:[email protected]...
> Thanks Norman,
>
> I've tried that but I get a merged cells complaint (although what
> few merged cells there are the same in each case) when I paste the values.
> I hate merged cells in VBA and don't use them often :-)
>
> Don
>
> "Norman Jones" <[email protected]> wrote in message
> news:u%[email protected]...
>> Hi Don,
>>
>> Try a dual copy / PasteSpecial operation: firstly values and then formats
>> or vice versa).
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Don Lloyd" <[email protected]> wrote in message
>> news:[email protected]...
>>> Hi,
>>>
>>> Scenario:
>>> 2 Workbooks with identical worksheets i.e. Same layout, same controls.
>>> The main difference is that Workbook 1 one has data. Workbook 2 doesn't.
>>>
>>> Problem
>>> How do I copy a range of data from workbook 1 to workbook 2, including
>>> cell formatting, without also copying the controls across.
>>>
>>> I've tried a straight range copy but I finish up with duplicate
>>> controls.
>>>
>>> If I try copying the values, I get a merged cells complaint (although
>>> what few merged cells there are the same in each case) - the formatting
>>> is required anyway.
>>>
>>> I can do it if the properties of the controls in workbook 1 are set to
>>> "don't move or size with cells" but it's not really practical.
>>>
>>> Thanks,
>>> Don
>>>
>>
>>
>
>

Thanks again,

With your help I think I can now work something out.

Don

"Norman Jones" <[email protected]> wrote in message
news:[email protected]...
> Hi Don,
>
>> I hate merged cells in VBA and don't use them often :-)
>
> I never use them.
>
>>>> I can do it if the properties of the controls in workbook 1 are set to
>>>> "don't move or size with cells" but it's not really practical.
>
> Why not write a simple macro to loop through the controls setting and then
> (after the copy operation) un-setting the property?
>
> ---
> Regards,
> Norman
>
>
>
> "Don Lloyd" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Norman,
>>
>> I've tried that but I get a merged cells complaint (although what
>> few merged cells there are the same in each case) when I paste the
>> values.
>> I hate merged cells in VBA and don't use them often :-)
>>
>> Don
>>
>> "Norman Jones" <[email protected]> wrote in message
>> news:u%[email protected]...
>>> Hi Don,
>>>
>>> Try a dual copy / PasteSpecial operation: firstly values and then
>>> formats or vice versa).
>>>
>>> ---
>>> Regards,
>>> Norman
>>>
>>>
>>>
>>> "Don Lloyd" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Hi,
>>>>
>>>> Scenario:
>>>> 2 Workbooks with identical worksheets i.e. Same layout, same controls.
>>>> The main difference is that Workbook 1 one has data. Workbook 2
>>>> doesn't.
>>>>
>>>> Problem
>>>> How do I copy a range of data from workbook 1 to workbook 2, including
>>>> cell formatting, without also copying the controls across.
>>>>
>>>> I've tried a straight range copy but I finish up with duplicate
>>>> controls.
>>>>
>>>> If I try copying the values, I get a merged cells complaint (although
>>>> what few merged cells there are the same in each case) - the
>>>> formatting is required anyway.
>>>>
>>>> I can do it if the properties of the controls in workbook 1 are set to
>>>> "don't move or size with cells" but it's not really practical.
>>>>
>>>> Thanks,
>>>> Don
>>>>
>>>
>>>
>>
>>
>
>