TRICKY: different data sets within the same page
 

Ranges A:D have fixed values. Ranges E:M have a mix of user input variables
and formulas.

I would like to create a toggle switch that allows a user to switch between
two sets of variables. It's essesntially like have two copies of Sheet 1.
The first copy would contain one set of data in the cells from E:M and the
second copy would contain a separate set of data. Both copies would contain
the same data in the cell from A:D.

The reason I would like to combine these into one page is because a user may
add or delete a row, or may create a different set of fixed values for
columns A:D...if this is done on only one page and not the other, the
comparisons become skewed. If it is combined into one page, Columns A:D
remain constant.

Matt

TRICKY: different data sets within the same page
 

What do you envision. Having the first data set in say AE:AM and the
second data set in BE:BM and then the toggle button copies one or the other
to E:M? The formulas in these to set would have to be adjusted so when
copied to E:M, they would refer to the correct cells.

Private Sub Toggle1_Click()
if Toggle1 = True then
Range("E:M").Copy Range("BE:BM")
Range("AE:AM").Copy Range("E:M")
else
Range("E:M").Copy Range("AE:AM")
Range("BE:BM").Copy Range("E:M)
End if
End Sub

--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Ranges A:D have fixed values. Ranges E:M have a mix of user input
variables
and formulas.

I would like to create a toggle switch that allows a user to switch
between
two sets of variables. It's essesntially like have two copies of Sheet 1.
The first copy would contain one set of data in the cells from E:M and the
second copy would contain a separate set of data. Both copies would
contain
the same data in the cell from A:D.

The reason I would like to combine these into one page is because a user
may
add or delete a row, or may create a different set of fixed values for
columns A:D...if this is done on only one page and not the other, the
comparisons become skewed. If it is combined into one page, Columns A:D
remain constant.

Matt

TRICKY: different data sets within the same page
 

Tom - Thanks again. Works well.

"Tom Ogilvy" wrote:

What do you envision. Having the first data set in say AE:AM and the
second data set in BE:BM and then the toggle button copies one or the other
to E:M? The formulas in these to set would have to be adjusted so when
copied to E:M, they would refer to the correct cells.

Private Sub Toggle1_Click()
if Toggle1 = True then
Range("E:M").Copy Range("BE:BM")
Range("AE:AM").Copy Range("E:M")
else
Range("E:M").Copy Range("AE:AM")
Range("BE:BM").Copy Range("E:M)
End if
End Sub

--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Ranges A:D have fixed values. Ranges E:M have a mix of user input
variables
and formulas.

I would like to create a toggle switch that allows a user to switch
between
two sets of variables. It's essesntially like have two copies of Sheet 1.
The first copy would contain one set of data in the cells from E:M and the
second copy would contain a separate set of data. Both copies would
contain
the same data in the cell from A:D.

The reason I would like to combine these into one page is because a user
may
add or delete a row, or may create a different set of fixed values for
columns A:D...if this is done on only one page and not the other, the
comparisons become skewed. If it is combined into one page, Columns A:D
remain constant.

Matt

TRICKY: different data sets within the same page
 

Tom...I did notice that when I added a bunch of rows, this procedure take a
couple of minutes to perform. I believe the cause is because I have some
built in data validation though the sub Worksheet_Change that runs everytime
certain columns have changes made to them. Is there a way to bypass this sub
(turn it off) while this copy/paste function runs then re-enable the sub?

Not sure if you will get this reply, so I am going to post it as a new
question titled "Bypass Worksheet_Change Sub"

Thanks,

Matt

"Tom Ogilvy" wrote:

What do you envision. Having the first data set in say AE:AM and the
second data set in BE:BM and then the toggle button copies one or the other
to E:M? The formulas in these to set would have to be adjusted so when
copied to E:M, they would refer to the correct cells.

Private Sub Toggle1_Click()
if Toggle1 = True then
Range("E:M").Copy Range("BE:BM")
Range("AE:AM").Copy Range("E:M")
else
Range("E:M").Copy Range("AE:AM")
Range("BE:BM").Copy Range("E:M)
End if
End Sub

--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Ranges A:D have fixed values. Ranges E:M have a mix of user input
variables
and formulas.

I would like to create a toggle switch that allows a user to switch
between
two sets of variables. It's essesntially like have two copies of Sheet 1.
The first copy would contain one set of data in the cells from E:M and the
second copy would contain a separate set of data. Both copies would
contain
the same data in the cell from A:D.

The reason I would like to combine these into one page is because a user
may
add or delete a row, or may create a different set of fixed values for
columns A:D...if this is done on only one page and not the other, the
comparisons become skewed. If it is combined into one page, Columns A:D
remain constant.

Matt

TRICKY: different data sets within the same page
 

Answered my own question. Application.EnableEvents.

thanks anyway.

Matt


"Matt" wrote:

Tom...I did notice that when I added a bunch of rows, this procedure take a
couple of minutes to perform. I believe the cause is because I have some
built in data validation though the sub Worksheet_Change that runs everytime
certain columns have changes made to them. Is there a way to bypass this sub
(turn it off) while this copy/paste function runs then re-enable the sub?

Not sure if you will get this reply, so I am going to post it as a new
question titled "Bypass Worksheet_Change Sub"

Thanks,

Matt

"Tom Ogilvy" wrote:

What do you envision. Having the first data set in say AE:AM and the
second data set in BE:BM and then the toggle button copies one or the other
to E:M? The formulas in these to set would have to be adjusted so when
copied to E:M, they would refer to the correct cells.

Private Sub Toggle1_Click()
if Toggle1 = True then
Range("E:M").Copy Range("BE:BM")
Range("AE:AM").Copy Range("E:M")
else
Range("E:M").Copy Range("AE:AM")
Range("BE:BM").Copy Range("E:M)
End if
End Sub

--
Regards,
Tom Ogilvy

"Matt" wrote in message
...
Ranges A:D have fixed values. Ranges E:M have a mix of user input
variables
and formulas.

I would like to create a toggle switch that allows a user to switch
between
two sets of variables. It's essesntially like have two copies of Sheet 1.
The first copy would contain one set of data in the cells from E:M and the
second copy would contain a separate set of data. Both copies would
contain
the same data in the cell from A:D.

The reason I would like to combine these into one page is because a user
may
add or delete a row, or may create a different set of fixed values for
columns A:D...if this is done on only one page and not the other, the
comparisons become skewed. If it is combined into one page, Columns A:D
remain constant.

Matt