From Mr. Ogilvy and Mr. deBruin, I have
worksheets("Sheet1").Cells.Cop
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormula
I'm having a little trouble with where to put it and how to put it there. I already have code on both sheets to color the cells based on text value. Do I add these two lines at the bottom of the existing code and if so which sheet do I add it to
Thanks in advance for any help
Erik

Hi Erik
this depends on what you're trying to achieve. If you want to invoke
this copy operation manually you may create the following sub:
sub do_copy()
worksheets("Sheet1").Cells.Copy
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormulas
end sub

And start this manually or assign a button to this macro.

--
Regards
Frank Kabel
Frankfurt, Germany

Erik wrote:
From Mr. Ogilvy and Mr. deBruin, I have:
worksheets("Sheet1").Cells.Copy
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormulas
I'm having a little trouble with where to put it and how to put it
there. I already have code on both sheets to color the cells based
on text value. Do I add these two lines at the bottom of the
existing code and if so which sheet do I add it to? Thanks in advance
for any help.
Erik

this code copies the ENTIRE contents of a sheet named "Sheet1" an
pastes it into cell "A1" on a sheet named "Sheet2" it would result i
Sheet2 becoming a clone of sheet 1.

if you want to copy a spceific AREA of an excel sheet, then you need t
specify it as a RANGE before you copy,

and then you would want to paste it to the First cell in a range on th
sheet you'd like to copy things to.

let's say you had info in cells A1 to D7 on a sheet called "Finance
and you wanted to copy it to a range that started at cell "Z20" on
sheet called "Monkey", for that, you'd alter Mr. Ogilvy's code a
follows:

'begin code

worksheets("finance").range("A1:D7").Copy
worksheets("monkey").Range("Z20").Pastespecial paste:=xlFormulas

'end code

you could add this to the end of the code you already have (righ
before the line that says "End Sub") if you like, or you could creat
another routine that just does that copy/paste operation

for that you'd just say :

'begin code

Sub CopyToZ20
worksheets("finance").range("A1:D7").Copy
worksheets("monkey").Range("Z20").Pastespecial paste:=xlFormulas

End Sub

'end code

Does that help you customize your code

--
Message posted from http://www.ExcelForum.com

Frank
I inserted the sub do_copy above the cell formatting sub on sheet2, but I can't seem to get any results. I am trying to get the values in the cells from sheet1 to automatically copy to the identical cells in sheet2. I tried it with a formula in each cell of sheet2, but the code didn't recognize the values an therefore didn't format the cell color. I have long since exceded my technical/programming knowledge, so please be explicit with any advice you may have. Thanks
Eri

----- Frank Kabel wrote: ----

Hi Eri
this depends on what you're trying to achieve. If you want to invok
this copy operation manually you may create the following sub
sub do_copy(
worksheets("Sheet1").Cells.Cop
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormula
end su

And start this manually or assign a button to this macro

-
Regard
Frank Kabe
Frankfurt, German

Erik wrote
From Mr. Ogilvy and Mr. deBruin, I have
worksheets("Sheet1").Cells.Cop
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormula
I'm having a little trouble with where to put it and how to put i
there. I already have code on both sheets to color the cells base
on text value. Do I add these two lines at the bottom of th
existing code and if so which sheet do I add it to? Thanks in advanc
for any help
Eri

Hi Erik
you may explain in total what you're trying to achieve. The code you
posted will just copy the formulas from sheet 1 to sheet 2. It will not
copy formats!

Where did you insert the code and how did you invoke this macro?


--
Regards
Frank Kabel
Frankfurt, Germany

Erik wrote:
Frank,
I inserted the sub do_copy above the cell formatting sub on sheet2,
but I can't seem to get any results. I am trying to get the values
in the cells from sheet1 to automatically copy to the identical cells
in sheet2. I tried it with a formula in each cell of sheet2, but the
code didn't recognize the values an therefore didn't format the cell
color. I have long since exceded my technical/programming knowledge,
so please be explicit with any advice you may have. Thanks. Erik

----- Frank Kabel wrote: -----

Hi Erik
this depends on what you're trying to achieve. If you want to
invoke this copy operation manually you may create the following
sub: sub do_copy()
worksheets("Sheet1").Cells.Copy
worksheets("Sheet2").Range("A1").Pastespecial paste:=xlFormulas
end sub

And start this manually or assign a button to this macro.

--
Regards
Frank Kabel
Frankfurt, Germany

Erik wrote:
From Mr. Ogilvy and Mr. deBruin, I have:
worksheets("Sheet1").Cells.Copy
worksheets("Sheet2").Range("A1").Pastespecial
paste:=xlFormulas
I'm having a little trouble with where to put it and how to
put it there. I already have code on both sheets to color the
cells based on text value. Do I add these two lines at the
bottom of the existing code and if so which sheet do I add it
to? Thanks in advance for any help.
Erik

Frank
I have two sheets that are identical except for the page layout. In each sheet I have code that formats the cell background color based on the text value in the cell. I want to copy exactly the value in each filled cell in sheet1 into the corresponding cell in sheet2 and then hopefully allow the same formatting code set the cell color in sheet2. My hope is to be able to enter the information in sheet1 and have sheet2 automatically look the same. This is the code I have already

Private Sub Worksheet_Change(ByVal Target As Range
Dim Num As Lon
Dim rng As Rang
Dim vRngInput As Varian
Set vRngInput = Intersect(Target, Range("B8:B19,D8:D19,F8:F19,H8:H19,J8:J19,L8:L19,N 8:N19")
If vRngInput Is Nothing Then Exit Su
For Each rng In vRngInpu
'Determine the colo
Select Case rng.Valu
Case Is = "SSH": Num = 3
Case Is = "SMH": Num = 3
Case Is = "SSO": Num = 2
Case Is = "SKMH": Num = 3
Case Is = "SA": Num = 4
Case Is = "SBC": Num = 4
Case Is = "HC": Num = 3
Case Is = "ADMIN": Num = 5
Case Is = "OC": Num = 1
End Selec
'Apply the colo
rng.Interior.ColorIndex = Nu
rng.Offset(0, 1).Interior.ColorIndex = Nu
Next rn
End Sub