Copy specific cells to another tab

[Mel]

I have a spreadsheet with 2 tabs. 'RATE-REVISION' and 'RATE-
REVISION-070'.

Cells G3, F5, J5, N5, P5 are set to copy directly to the same cells
int he Rate-Revision070 sheet.

Cells C9 to F88 are completed along with cells H9 to K9 are also
completed (if there is data to complete). Cells L9 to M88 have the
work week pre-filled but may be changed if required. These fields
would also be copied to the Rate-Revision070 tabl

All fields are exactly the same in both sheets.

I also have another macro that is set to a 'clear button' to clear the
fields in either sheet. Cells F9 to F88 has a formula that is pre-
filled by the clear macro.

I had tried to set up a macro that would do this automatically but I
have not been sucessfull. Any help would be appreciated.
thx

Mel

 

[GS]

Mel pretended :

I have a spreadsheet with 2 tabs. 'RATE-REVISION' and 'RATE-
REVISION-070'.

Cells G3, F5, J5, N5, P5 are set to copy directly to the same cells
int he Rate-Revision070 sheet.

Cells C9 to F88 are completed along with cells H9 to K9 are also
completed (if there is data to complete). Cells L9 to M88 have the
work week pre-filled but may be changed if required. These fields
would also be copied to the Rate-Revision070 tabl

All fields are exactly the same in both sheets.

I also have another macro that is set to a 'clear button' to clear the
fields in either sheet. Cells F9 to F88 has a formula that is pre-
filled by the clear macro.

I had tried to set up a macro that would do this automatically but I
have not been sucessfull. Any help would be appreciated.
thx

Mel

Did you try doing it manually with the Macro Recorder running?

 

[Mel]

Mel pretended :












Did you try doing it manually with the Macro Recorder running?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

yes. But I need it to do this automatically when it the initial sheet
is completed. The user Opens the spreadsheet, then on the first
tab starts to complete fields. I have 4 tombstone fields that are set
to '= cell on first sheet' but the rest of the sheet I do not have
set. I need it to automatically copy over the anything in cells B9 to
M88.

Mel

 

[GS]

Mel expressed precisely :

yes. But I need it to do this automatically when it the initial sheet
is completed. The user Opens the spreadsheet, then on the first
tab starts to complete fields. I have 4 tombstone fields that are set
to '= cell on first sheet' but the rest of the sheet I do not have
set. I need it to automatically copy over the anything in cells B9 to
M88.

Mel

I see. If you just need to duplicate the data in Sheet1 to the same
location in Sheet2 you can either group those 2 sheets while users fill
in Sheet1's fields, OR run a macro after revisions are made to Sheet1
that will update the same cells in Sheet2, OR simply copy Sheet1.

To populate the same range on Sheet2...

Sheets("Sheet2").Range("B9:M88") = Sheets("Sheet1").Range("B9:M88")

 

[James Ravenswood]

Mel expressed precisely :








I see. If you just need to duplicate the data in Sheet1 to the same
location in Sheet2 you can either group those 2 sheets while users fill
in Sheet1's fields, OR run a macro after revisions are made to Sheet1
that will update the same cells in Sheet2, OR simply copy Sheet1.

To populate the same range on Sheet2...

  Sheets("Sheet2").Range("B9:M88") = Sheets("Sheet1").Range("B9:M88")

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry:

The following does not work:

Sub NotGood()
Sheets("Sheet2").Range("B9:M88") = Sheets("Sheet1").Range("B9:M88")
End Sub

But this does:

Sub LittleBetter()
Sheets("Sheet2").Range("B9:M88").Value =
Sheets("Sheet1").Range("B9:M88").Value
End Sub

Sadly formatting and formulas get lost.

 

[GS]

James Ravenswood explained on 12/22/2011 :

Hi Garry:

The following does not work:

Sub NotGood()
Sheets("Sheet2").Range("B9:M88") = Sheets("Sheet1").Range("B9:M88")
End Sub

But this does:

Sub LittleBetter()
Sheets("Sheet2").Range("B9:M88").Value =
Sheets("Sheet1").Range("B9:M88").Value
End Sub

Sadly formatting and formulas get lost.

Thank you James! Yes, I keep forgetting that when we ref ranges
directly in this fashion we must include the .Value property. MY BAD! I
know this only too well but because I rarely copy data this way I
almost always forget, ..only to find out the hard way to include .Value
property in this case.

 

[GS]

James,
Your last comment grabbed me and wouldn't let go! We can copy all and
convert to values as follows:

Sheets("Sheet1").Range("B9:M88").Copy _
Destination:=Sheets("Sheet2").Range("B9:M88")
Sheets("Sheet2").Range("B9:M88").Value = _
Sheets("Sheet2").Range("B9:M88").Value