passing spreadsheet cell data to macro

[Neal]

I have a macro to hide a column in an array of worksheets. I would like to have
it get the column from a cell in a worksheet. How do I go about doing that? In
other words, If cell N42 has a J in it, then the J would be transferred to the
macro.

Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
sh.Columns("g:g").Hidden = True
Next


Thanks

Neal

 

[Bob Phillips]

Neal,

vCol = Range("N42").Value & ":" & Range("N42").Value
For Each sh in ActiveWindow.SelectedSheets
sh.Columns(vCol).Hidden = True
Next

 

[Tom Ogilvy]

Dim sh as Worksheet, sCol as String
sCol = Trim(Worksheets("Sheet1").Range("N42").Value)
for each sh in ActiveWindow.SelectedSheets
sh.Columns(sCol & ":" & sCol).Hidden = True
Next

 

[Neal]

Thanks for the help but I am getting a (Runtime error 13 Type Mismatch) when
the macro gets to
sh.Columns(vCol).Hidden = True
What could be causing this?

Neal

 

[Neal]

Thank you Tom. It works perfectly. I certainly have a lot to learn about
macros.

Neal

 

[Tom Ogilvy]

Also, here is my answer to your explanation in the original thread:

Assume you have a sheet (named Data) with 37 cells in column B containing an
X if you want a column hidden. If I want column B hidden, I put an X in B2,
if I want column AA hidden, I put an X in B27.

You can have the 37 labels in column A, but that is not needed by the macro.
Assume this sheet is named "data" and the only other sheets in the workbook
are the 7 sheets.

Sub HandleColumns()
Dim varr()
Dim sStr As String, rng As Range
Dim sh As Worksheet
On Error Resume Next
With Worksheets("Data")
Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _
SpecialCells(xlConstants, xlTextValues)
End With
On Error GoTo 0
if rng is nothing then exit sub
ReDim varr(1 To rng.Count)
sStr = ""
For Each cell In rng
sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27))
sStr = sStr & sCol & ":" & sCol & ","
Next
sStr = Left(sStr, Len(sStr) - 1)
If Len(Trim(sStr)) = 0 Then Exit Sub
For Each sh In Worksheets
If LCase(sh.Name) <> "data" Then
sh.Columns.Hidden = False
sh.Range(sStr).EntireColumn.Hidden = True
End If
Next

End Sub