UserRange.Columns.Count help

[Prof Reid]

Thanks for looking--

In the VBA below, I attempt to make a new worksheet--"Labels", and then populate
it a column at a time, from an existing worksheet--"Sheet4".

The problem I'm having is that the value returned by
Worksheets("Labels").UsedRange.Columns.Count does not
increase to account for the added columns pasted in.
Is there something I have to do to explicitly update
the Columns.Count?

A minor problem is that the original "Labels" sheet added says
there is already one (1) column just after creation, as opposed
to zero (0). Maybe I'll just have to live with that one .

I could accomplish the below, by just putting the columns from
"Sheet4" into explicit columns ("A:A", "B:B", ...), but I'd
like to be able to let the users add some optional additional
columns to "Sheets", hence this try at an incremental-add version.

Feel free to criticize any of the coding, maybe with references,
so that I might eventually get better at this.

Thanks,

Dick

**************************************************************************

Sub MakeLabelsSheet()
Dim lastCol As Long
Sheets("Sheet4").Select
Sheets.Add
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Labels"
lastCol = Worksheets("Labels").UsedRange.Columns.Count

Debug.Print "Labels sheet just made with lastCol = ", lastCol

Call AddNamedColumn("MBR_NAMELINE")
Call AddNamedColumn("MBR_PLINE1")
Call AddNamedColumn("MBR_PLINE2")
Call AddNamedColumn("MBR_CSZ")
End Sub

Sub AddNamedColumn( colName As String )

Dim labelRow
Dim col As Long
Dim lastCol As Long
Dim destCol As Long

lastCol = Worksheets("Labels").UsedRange.Columns.Count
destCol = lastCol + 1

Set labelRow = Worksheets("Sheet4").Range("A1:AZ1")

col = Application.WorksheetFunction.Match(colName, labelRow, 0)

Debug.Print "Adding from WS4-column = ", col, ": ", colName, _
" to Labels-column = ", destCol

Sheets("Sheet4").Select
Columns(col).Copy
Sheets("Labels").Select
Columns(destCol).Select
ActiveSheet.Paste
Sheets("Sheet4").Select
Application.CutCopyMode = False
Sheets("Labels").Select

End Sub

 

[Ron de Bruin]

Hi Prof Reid

The Usedrange is not always what you think

See this page
http://www.rondebruin.nl/last.htm

 

[Susan]

Prof -
did you get this worked out from ron's site??
susan

 

[Ron de Bruin]

Hi Susan

Yes, he send me a private thank you

 

[Susan]

Hi Susan

Yes, he send me a private thank you

oh good! otherwise i had some free time i could help him.

susan