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

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 segmentin
data by a data element; in your case the data element is days of th
week.

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

In your case, the answer could be accomplished in any number of way
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 Databas
Query...

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

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

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

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