Hello,

Is there a way to make
Set sourceRange = Sheets("Sheet1").Columns("D:D")
reference column D in all worksheets instead of just "Sheet1" ? I'm
looking for a global 'all sheets in workbook' command and can't seem to
find one. I realize that I can list all the sheets by individual name
Sheets(Array("Sheet2", "Main", "Control Sheet", "ZDV 08 FL270-359", _
"ZDV 08 FL270-369", "ZDV 08 FL270-379"))
OR
Sheets(Sheet2", "Main", "Control Sheet", "ZDV 08 FL270-359", _
"ZDV 08 FL270-369", "ZDV 08 FL270-379")
but I wanted to try and do this in one shot.

Any help is appreciated :-)
Thanks,
Bill

PS Here is the code that I found online and am trying to adapt to copy
column D from all sheets to a collection sheet where it will insert in the
next empty colum:
Sub CopyColumnew()
Dim sourceRange As Range
Dim destrange As Range
Dim Lc As Integer
Lc = Lastcol(Sheets("Main")) + 1
Set sourceRange = Sheets("Sheet1").Columns("D:D")
Set destrange = Sheets("Main").Columns(Lc)
sourceRange.Copy destrange
End Sub

Sub CopyColumnValues()
Dim sourceRange As Range
Dim destrange As Range
Dim Lc As Integer
Lc = Lastcol(Sheets("Main")) + 1
Set sourceRange = Sheets("Sheet1").Columns("D:D")
Set destrange = Sheets("Main").Columns(Lc). _
Resize(, sourceRange.Columns.Count)
destrange.Value = sourceRange.Value
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("D1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("D1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

--
Message posted via http://www.officekb.com