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

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?

--
Garry

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

On Dec 21, 3:23*pm, GS <g...@somewhere.net> wrote:
> 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?
>
> --
> 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

Mel expressed precisely :
> On Dec 21, 3:23*pm, GS <g...@somewhere.net> wrote:
>> 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?
>>
>> --
>> 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

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 at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Dec 22, 8:57*am, GS <g...@somewhere.net> wrote:
> Mel expressed precisely :
>
>
>
>
>
> > On Dec 21, 3:23*pm, GS <g...@somewhere.net> wrote:
> >> 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?
>
> >> --
> >> 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
>
> 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.

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.

--
Garry

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

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

--
Garry

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