Excel 97 Macro

boydr · Apr 28th, 2002, 07:05 PM

Could someone tell me why that when I try to copy the value of a cell from one sheet to another, the macro runs fine, but nothing gets copied? The source range contains a formula, but I just want the values copied over.

Another problem I have: , for example, I'm trying to copy the range A1:C1 on sheet1 to D4:F4 on sheet2 and it says that the cells aren't the same size. Does the column width have to be the same size? I'm coping from a range of 3 cells to another range of 3 cells but I don't get it.

[ This Message was edited by: boydr on 2002-04-28 18:07 ]

Tom Urtis · Apr 28th, 2002, 07:22 PM

Regarding the first part of your post, the best thing you could do is post your code so we can see what is happening, and then make recommendations. I suspect that all you should have to do is to select the upper left cell of the destination range, instead of the entire destination range, and that might clear up that issue, unless I'm not understanding your problem.

Regarding the second half of your post, try this approach:

Sub CopyIt()
Sheet2.Range("D4:F4").Value = Sheet1.Range("A1:C1").Value
End Sub

Any help?

Tinhare · Apr 28th, 2002, 08:29 PM

If you have merged cells that can cause a problem.

Cheers,
Alan.

boydr · Apr 29th, 2002, 06:15 AM

I do have merged cells on both the source and destination. Why would that present a problem?

Tinhare · Apr 29th, 2002, 08:43 AM

I think it is because when a cell is merged Excel can't figure out which of the merged cells you want to paste to, but if the merged cells are in the same sequence instead of pasting to the whole range ie D4:F4 just paste to D4 and it should work.
for example if A1 and B1 are merged and D4 and E4 are merged then copying A1:C1 and pasting to just D4 will work correctly. If however the merged cells do not line up then the merged cells on the destination sheet will be changed to match the originating sheet.

Cheers,
Alan.

boydr · Apr 29th, 2002, 07:18 PM

Thanks. I'll give it a shot in a day or two. I'm so busy I can't think straight right now.

Apr 28th, 2002, 07:22 PM	#2
Tom Urtis MrExcel MVP Join Date: Feb 2002 Location: San Francisco, California USA Posts: 10,387	Regarding the first part of your post, the best thing you could do is post your code so we can see what is happening, and then make recommendations. I suspect that all you should have to do is to select the upper left cell of the destination range, instead of the entire destination range, and that might clear up that issue, unless I'm not understanding your problem. Regarding the second half of your post, try this approach: Sub CopyIt() Sheet2.Range("D4:F4").Value = Sheet1.Range("A1:C1").Value End Sub Any help? __________________ Tom Urtis Microsoft MVP - Excel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 28th, 2002, 07:05 PM	#1
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Could someone tell me why that when I try to copy the value of a cell from one sheet to another, the macro runs fine, but nothing gets copied? The source range contains a formula, but I just want the values copied over. Another problem I have: , for example, I'm trying to copy the range A1:C1 on sheet1 to D4:F4 on sheet2 and it says that the cells aren't the same size. Does the column width have to be the same size? I'm coping from a range of 3 cells to another range of 3 cells but I don't get it. [ This Message was edited by: boydr on 2002-04-28 18:07 ]

Apr 28th, 2002, 08:29 PM	#3
Tinhare New Member Join Date: Apr 2002 Location: England Posts: 44	If you have merged cells that can cause a problem. Cheers, Alan.

Apr 29th, 2002, 06:15 AM	#4
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	I do have merged cells on both the source and destination. Why would that present a problem?

Apr 29th, 2002, 08:43 AM	#5
Tinhare New Member Join Date: Apr 2002 Location: England Posts: 44	I think it is because when a cell is merged Excel can't figure out which of the merged cells you want to paste to, but if the merged cells are in the same sequence instead of pasting to the whole range ie D4:F4 just paste to D4 and it should work. for example if A1 and B1 are merged and D4 and E4 are merged then copying A1:C1 and pasting to just D4 will work correctly. If however the merged cells do not line up then the merged cells on the destination sheet will be changed to match the originating sheet. Cheers, Alan.

Apr 29th, 2002, 07:18 PM	#6
boydr Board Regular Join Date: Apr 2002 Location: Arkansas Posts: 157	Thanks. I'll give it a shot in a day or two. I'm so busy I can't think straight right now.