Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis

Hi franciz

For others this is the code page (last example)
http://www.rondebruin.nl/copy3.htm

We can add one line

Replace this:
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue


With:

'Filter the range on the FilterField column
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue

sourceRange.AutoFilter Field:=3, Criteria1:="<"





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis

Hi Ron,

Thanks, this work great. I need two more modification in the excellent codes
of yours.

Is it also possible to have the header include, my source files headers in
on row 2. I have tried to modify the below to include a header but was
unsucessful.

' Set a range without the Header row
Set rng = .Resize(.Rows.Count - 1,
..Columns.Count). _
Offset(1,
0).SpecialCells(xlCellTypeVisible)

In addition, I would like the new workbook name as "Utility" rather than
"Sheet1".
Embarrass to say that I could not find the codes that mentioned naming the
workbook as "Sheet1"

Thank you for your assistance

regards, francis

"Ron de Bruin" wrote:

Hi franciz

For others this is the code page (last example)
http://www.rondebruin.nl/copy3.htm

We can add one line

Replace this:
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue


With:

'Filter the range on the FilterField column
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue

sourceRange.AutoFilter Field:=3, Criteria1:="<"





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis

Use

Set rng = .SpecialCells(xlCellTypeVisible)


You must save the file before Sheet1 will be changed
If you create a one sheet workbook like I do in the code the name is automatic Sheet1

After this line
BaseWks.Columns.AutoFit

Add
BaseWks.SaveAs "C:\Utility.xls"

If you want to close it add this one also
BaseWks.Close False

What do you want to do if there is alrewady a file with that name?
Replace ?

You can add the date/time to the file name for example


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks, this work great. I need two more modification in the excellent codes
of yours.

Is it also possible to have the header include, my source files headers in
on row 2. I have tried to modify the below to include a header but was
unsucessful.

' Set a range without the Header row
Set rng = .Resize(.Rows.Count - 1,
.Columns.Count). _
Offset(1,
0).SpecialCells(xlCellTypeVisible)

In addition, I would like the new workbook name as "Utility" rather than
"Sheet1".
Embarrass to say that I could not find the codes that mentioned naming the
workbook as "Sheet1"

Thank you for your assistance

regards, francis

"Ron de Bruin" wrote:

Hi franciz

For others this is the code page (last example)
http://www.rondebruin.nl/copy3.htm

We can add one line

Replace this:
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue


With:

'Filter the range on the FilterField column
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue

sourceRange.AutoFilter Field:=3, Criteria1:="<"





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis

Hi Ron,

Thank for assisting in this, I appreciate your effort and patience.

1) Using this line : Set rng = .SpecialCells(xlCellTypeVisible)
will include header row which is on the row 1 of the source files, but
my header row from the source files start at row 2. How do I change this?

2) Quote " You can add the date/time to the file name for example "

This is a good idea as it allow me to save different file names and keep it
for
a period just in case I need to refer back to what have been done.How do I
add this into the line. Appreciate your help in this.


Thanks in advance

regards, francis



"Ron de Bruin" wrote:

Use

Set rng = .SpecialCells(xlCellTypeVisible)


You must save the file before Sheet1 will be changed
If you create a one sheet workbook like I do in the code the name is automatic Sheet1

After this line
BaseWks.Columns.AutoFit

Add
BaseWks.SaveAs "C:\Utility.xls"

If you want to close it add this one also
BaseWks.Close False

What do you want to do if there is alrewady a file with that name?
Replace ?

You can add the date/time to the file name for example


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks, this work great. I need two more modification in the excellent codes
of yours.

Is it also possible to have the header include, my source files headers in
on row 2. I have tried to modify the below to include a header but was
unsucessful.

' Set a range without the Header row
Set rng = .Resize(.Rows.Count - 1,
.Columns.Count). _
Offset(1,
0).SpecialCells(xlCellTypeVisible)

In addition, I would like the new workbook name as "Utility" rather than
"Sheet1".
Embarrass to say that I could not find the codes that mentioned naming the
workbook as "Sheet1"

Thank you for your assistance

regards, francis

"Ron de Bruin" wrote:

Hi franciz

For others this is the code page (last example)
http://www.rondebruin.nl/copy3.htm

We can add one line

Replace this:
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue


With:

'Filter the range on the FilterField column
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue

sourceRange.AutoFilter Field:=3, Criteria1:="<"





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis

Hi francis


Change

RangeAddress = Range("A1:G" & Rows.Count).Address

To

RangeAddress = Range("A2:G" & Rows.Count).Address
Change the Column to yours


Use this to save

BaseWks.SaveAs "C:\Utility " & Format(Now, "yyyy-mm-dd h-mm-ss") & ".xls"



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thank for assisting in this, I appreciate your effort and patience.

1) Using this line : Set rng = .SpecialCells(xlCellTypeVisible)
will include header row which is on the row 1 of the source files, but
my header row from the source files start at row 2. How do I change this?

2) Quote " You can add the date/time to the file name for example "

This is a good idea as it allow me to save different file names and keep it
for
a period just in case I need to refer back to what have been done.How do I
add this into the line. Appreciate your help in this.


Thanks in advance

regards, francis



"Ron de Bruin" wrote:

Use

Set rng = .SpecialCells(xlCellTypeVisible)


You must save the file before Sheet1 will be changed
If you create a one sheet workbook like I do in the code the name is automatic Sheet1

After this line
BaseWks.Columns.AutoFit

Add
BaseWks.SaveAs "C:\Utility.xls"

If you want to close it add this one also
BaseWks.Close False

What do you want to do if there is alrewady a file with that name?
Replace ?

You can add the date/time to the file name for example


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi Ron,

Thanks, this work great. I need two more modification in the excellent codes
of yours.

Is it also possible to have the header include, my source files headers in
on row 2. I have tried to modify the below to include a header but was
unsucessful.

' Set a range without the Header row
Set rng = .Resize(.Rows.Count - 1,
.Columns.Count). _
Offset(1,
0).SpecialCells(xlCellTypeVisible)

In addition, I would like the new workbook name as "Utility" rather than
"Sheet1".
Embarrass to say that I could not find the codes that mentioned naming the
workbook as "Sheet1"

Thank you for your assistance

regards, francis

"Ron de Bruin" wrote:

Hi franciz

For others this is the code page (last example)
http://www.rondebruin.nl/copy3.htm

We can add one line

Replace this:
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue


With:

'Filter the range on the FilterField column
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue

sourceRange.AutoFilter Field:=3, Criteria1:="<"





--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"franciz" wrote in message ...
Hi all

I am using the the codes in Merge a range from all workbooks in a folder
with AutoFilter provided by Ron de Bruin

Is it possible to have two auto filters enable in the codes? if tes,
How do I write the additional syntax to filter for NonBlanks rows in column
C given that the current codes provided wrote as :

FilterField = 2
SearchValue = "Y"

Thanks for your assistance

regards, francis