Hiding A Column So It Does Not Receive Pastes?

[(PeteCresswell)]

Got a spreadsheet that people are using to enter rates of return for bond funds.

Column 1 is the name of the fund.

Columns 2 through, say, 250 are returns for a given date (i.e. the column
headers are dates).


Users of the sheet are entering data partially by pasting blocks of numbers from
other sources which do not include Saturdays and Sundays.

The sheet in question, however, has columns for *all* dates in a range -
including weekends - and I'd like to keep it that way.

First thing that occurred to me was to hide the weekend columns, but when I
tried a block paste, some of the numbers went into hidden columns as if they
were visible.

I *could* just not create columns for weekends, but for various programming
reasons it would be easier and less complex to have them there.

Anybody been here?

Is there a way to make a column invisible not only to the user's eye but also to
the paste process?

 

[Don Guillett]

try this idea
Sub hideweekenddays()
Columns.Hidden = False
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lc To 1 Step -1
If Weekday(Cells(1, i)) = 1 _
Or Weekday(Cells(1, i)) = 7 _
Then Columns(i).Hidden = True
Next
End Sub

 

[(PeteCresswell)]

Per Don Guillett:

try this idea
Sub hideweekenddays()
Columns.Hidden = False
lc = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lc To 1 Step -1
If Weekday(Cells(1, i)) = 1 _
Or Weekday(Cells(1, i)) = 7 _
Then Columns(i).Hidden = True
Next
End Sub

What I need is for the hidden columns not to receive data when the user pasts in
a range.

 

[Dave Peterson]

Lock the cells in those columns and protect the worksheet???

 

[(PeteCresswell)]

Per Dave Peterson:

Lock the cells in those columns and protect the worksheet???

But then the user cannot type anything into the cells, right?

I want the user tb able to type stuff into the cells and/or put formulas behind
them - yet have the sheet protected so they can't insert columns/rows or move
columns/rows around.

 

[Dave Peterson]

Lock the cells in the columns that are hidden and unlock the cells that can be
changed. Then protect the worksheet.

And if you're using xl2002+, make sure you don't allow the user to insert/delete
rows or columns.

 

[(PeteCresswell)]

Per Dave Peterson:

Lock the cells in the columns that are hidden and unlock the cells that can be
changed. Then protect the worksheet.

Mea Culpa. That was in your previous post but I didn't pick up on the idea
that I'd only be locking the hidden cells.

Gonna give it a try this afternoon.

Thanks.