adding columns

  • Thread starter Thread starter david
  • Start date Start date
D

david

I am trying to build a summary page for a bunch of
worksheets. Each sheet in the workbook is a for a
different company. I want to take information out of one
of those columns and cut it and paste it on the summary
page. Then I want to take the numbers input and get an
average for it in the last column.

Thanks,

David
 
Not sure how your data is laid out, but this might help.

Option Explicit
Sub testme01()

Dim newWks As Worksheet
Dim wks As Worksheet
Dim iCtr As Long
Dim ColToReturn As Long
Dim dummyRng As Range

Set newWks = Worksheets.Add

ColToReturn = 5

iCtr = 2 'start in B and put averages in column A
For Each wks In ActiveWorkbook.Worksheets
If wks.Name = newWks.Name Then
'do nothing
Else
wks.Columns(ColToReturn).Copy _
Destination:=newWks.Cells(1, iCtr)
iCtr = iCtr + 1
End If
Next wks

With newWks
.Name = "AVG_" & Format(Now, "yyyymmdd_hhmmss")
Set dummyRng = .UsedRange 'try to reset last cell
.Range("a1:a" & .Cells.SpecialCells(xlCellTypeLastCell).Row).Formula _
= "=average(b1:iv1)"
End With

End Sub

I stuck the average into column A. (I think it's easier to find that way.)
 
Back
Top