Sorting and Summarizing data

Hello,

I have a workbook with no more tha five worksheets. The first worksheet is going to be a summary. The remaining four sheets are each day of the workweek.

I want to write a macro that will look at a column of data from one of the workweek worksheets and copy it the summary worksheet. The only catch is that I only want to copy it if the first character is the letter Y. Can anyone give me some help or point me in the right direction, such as an array or some tips like that.

Thanks in advance.

Try this:

Sub SelectY()

Set ws1 = Worksheets("Sheet1") ' One of your workweek sheets
Set ws2 = Worksheets("Sheet2") ' Your Summary sheet

' Assume data is in column A
Set rng = ws1.Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)

' Set range to collate "Y" cells
Set Yrng = Nothing

For Each c In rng ' Loop through your input data

If Left(c, 1) = "Y" Then ' Starts with "Y" ....
If Yrng Is Nothing Then
Set Yrng = c
Else
Set Yrng = Union(Yrng, c)
End If
End If

Next c
' Set output column e.g. D1 in your Summary sheet
Set orng = ws2.Range("d1")
Yrng.Copy orng

End Sub


HTH
"bundyloco" wrote:

>
> Hello,
>
> I have a workbook with no more tha five worksheets. The first worksheet
> is going to be a summary. The remaining four sheets are each day of the
> workweek.
>
> I want to write a macro that will look at a column of data from one of
> the workweek worksheets and copy it the summary worksheet. The only
> catch is that I only want to copy it if the first character is the
> letter Y. Can anyone give me some help or point me in the right
> direction, such as an array or some tips like that.
>
> Thanks in advance.
>
>
> --
> bundyloco
> ------------------------------------------------------------------------
> bundyloco's Profile: http://www.excelforum.com/member.php...o&userid=23386
> View this thread: http://www.excelforum.com/showthread...hreadid=376701
>
>

bundyloco,

Excel is so easy to use -- it's ALSO so easy to make mistakes.

One big mistake that I have observed many times over, is segmenting
data by a data element; in your case the data element is days of the
week.

More than likely, you and your organization/company, would be much
better served by keeping your data together in a single table in your
Excel database.

In your case, the answer could be accomplished in any number of ways
and far easier if ALL your data were in a single sheet.

I'd do a simple MS Query, using Data/Get External Data/New Database
Query...

In the Query Grid, I'd set a Criteria on the Left(YourFirstCol, 1) with
a value of 'Y'

There's no VB code required for this technique and the query will
Refresh, returning results no matter how many rows of data ther are in
your source sheet.

Alternatively, if you can't or won't consolidate your data, the query
can be written as a UNION of the 4 sheets, using the same criteria in
each Select.


--
SkipVought
------------------------------------------------------------------------
SkipVought's Profile: http://www.msusenet.com/member.php?userid=1989
View this thread: http://www.msusenet.com/t-1870513968