combining worksheets-write a VBA to populate column B.

[zapszipszops]

Hi Everyone,

I need some help. I have a workbook that contains a worksheet for each day of the Month. eg. March 1 is named 01, March 2 is named 02 and so on. I would like to add a summary worksheet that will List all the dates in Column A, and in Column B the number of calls received for that day. Each worksheet is formatted exactly the same and the information for column B can be found in cell B1 of each worksheet. I am not very good at VBA. Could someone help me write a VBA to populate column B.

Thanks,
Julie

[Nigel]

One way ...........this uses the value of the sheet names 01,02,03 etc) to
position each day on the summary sheet in the correct row, avoiding any
sortation and if there are gaps missing days. The summary sheet is renewed
each time the process is run and created if it does not exist. It transfers
the value in B1 on each sheet to the related row on the summary sheet.


Sub SummaryDate()
Dim wS As Worksheet, sumCreate As Boolean

' check if summary sheet exists
sumCreate = True
For Each wS In ActiveWorkbook.Sheets
If wS.Name = "Summary" Then sumCreate = False
Next

' if no summary sheet add it at start
If sumCreate Then
Worksheets.Add before:=Sheets(1)
ActiveSheet.Name = "Summary"
End If

' clear then fill the summary sheet
Sheets("Summary").Cells.ClearContents
For Each wS In ActiveWorkbook.Sheets
If Val(wS.Name) >= 1 And Val(wS.Name) <= 31 Then
With Sheets("Summary")
.Cells(Val(wS.Name), 1) = wS.Name
.Cells(Val(wS.Name), 2) = wS.Range("B1")
End With
End If
Next

End Sub

--
Cheers
Nigel



"zapszipszops" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Everyone,
>
> I need some help. I have a workbook that contains a worksheet for each
> day of the Month. eg. March 1 is named 01, March 2 is named 02 and so
> on. I would like to add a summary worksheet that will List all the
> dates in Column A, and in Column B the number of calls received for
> that day. Each worksheet is formatted exactly the same and the
> information for column B can be found in cell B1 of each worksheet. I
> am not very good at VBA. Could someone help me write a VBA to populate
> column B.
>
> Thanks,
> Julie
>
>
> --
> zapszipszops
> ------------------------------------------------------------------------
> zapszipszops's Profile:
> http://www.excelforum.com/member.php...o&userid=33217
> View this thread: http://www.excelforum.com/showthread...hreadid=530364
>

[zapszipszops]

Wow, that's awesome! Thanks Nigel