Copy & Paste Issues

[stapuff]

I use each one of the pieces of code below in my project. The middle piece is the only one that when pasted shows any formula's in it. Why? It is copying the same range and pasting it to different workbooks. I have stepped through the code several times. I can not for the life of me figure this out.

Any help would be greatly appreciated.

Kurt


Sheets("sheet1").Select
Range("A2:I200").Select
Selection.Copy
Windows("Small.xls").Activate
Range("A2").Select
ActiveSheet.Paste


Sheets("sheet1").Select
Range("A2:I200").Select
Selection.Copy
Windows("Medium.xls").Activate
Range("A2").Select
ActiveSheet.Paste


Sheets("sheet1").Select
Range("A2:I200").Select
Selection.Copy
Windows("Large.xls").Activate
Range("A2").Select
ActiveSheet.Paste

 

[HalfAce]

Hi Kurt,
I don't see any reason why one would be any different than the others.
Do you want them all to paste formulas, or none of them to?

Try replacing what you're using with one of these.
1 - For all of them to retain any formulas present:

Sub Test()
Dim Rng As Range
Sheets("Sheet1").Activate
Set Rng = [A2:I200]
Workbooks("Small.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Formula
Workbooks("Medium.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Formula
Workbooks("Large.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Formula
End Sub

Or...
2 - For none of them to retain any formulas:

Sub Test()
Dim Rng As Range
Sheets("Sheet1").Activate
Set Rng = [A2:I200]
Workbooks("Small.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Value
Workbooks("Medium.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Value
Workbooks("Large.xls").Sheets("Sheet1").[A2:I200].Value = Rng.Value
End Sub

Maybe one or the other will help,
Dan

 

[stapuff]

Dan -

Thanks for the post back. I can't for the life of me understand why 1 is doing it differently either. I will try your 2nd suggestion. I want everything but the formula's.

Thanks again for your post,

Kurt

 

[stapuff]

Dan -

You suggestion worked on getting rid of the formula's but now I lost all of my cell coloring.

In column I - I have cells filled with the color of the cell value. If "Green" cell filled green, If "Blue" then blue filled, etc.

I want the code to copy the range and paste everything except formula's. (Color, font, format, values).

What do I need to do?

Thanks for your help.

Kurt

 

[HalfAce]

Ah... I see.
Then maybe it's best to go back to what you had originally, with a bit of a twist.
I'm thinking something along the lines of:

Sub Test()
Sheets("Sheet1").Select
Range("A2:I200").Copy

Windows("Small.xls").Activate
Range("A2").Select
ActiveSheet.Paste
Selection.Value = Selection.Value

Windows("Medium.xls").Activate
Range("A2").Select
ActiveSheet.Paste
Selection.Value = Selection.Value

Windows("Large.xls").Activate
Range("A2").Select
ActiveSheet.Paste
Selection.Value = Selection.Value

End Sub

This will copy everything, (formats, values, formulas, etc) and then convert the formulas to values after pasting.

Hope it helps,
Dan

 

[stapuff]

Thanks Dan -

Works like a charm.

Kurt