TRICKY: different data sets within the same page

[Guest]

Ranges A 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.

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...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
remain constant.

Matt

 

[Tom Ogilvy]

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

 

[Guest]

Tom - Thanks again. Works well.

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

 

[Guest]

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

 

[Guest]

Answered my own question. Application.EnableEvents.

thanks anyway.

Matt