Merge all files in a folder
 

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

Merge all files in a folder
 

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

Merge all files in a folder
 

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

Merge all files in a folder
 

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

Merge all files in a folder
 

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

Merge all files in a folder
 

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

Merge all files in a folder
 

Hi Ron,

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

Hi Ron,

I was having problem to respond as I was not able to access the reply page
after I click on reply, not sure why.

Thank for your assistance, your codes works excellently.
The reason for additional sheets is because I will copy a table in sheet1 of
another workbook, named "OLT" to a new sheet here. This is for a lookup on
customer names in Column C and its related rates in Column A of the "OLT"
workbook, so that VLookup can look at the table and search the customer names
in Column B and return the related rates in Column M of the active workbook,
named "Utility yyyy-mm-dd h-mm-ss"

Another option is to look at workbook "OLT" without copying the table to the
active
workbook, but not sure is this possible and should the other workbook be open?

What do you think? Your suggestion and input is very appreciate and valuable.

Another request is the the current macro will filter as :

sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
FilterField = 12
SearchValue = "m"

I have add the following filter :
sourceRange.AutoFilter Field:=3, Criteria1:="Y"
sourceRange.AutoFilter Field:=2, Criteria1:="<"

I need to also filter for Criteria1:="c" in Column L by adding this line

sourceRange.AutoFilter Field:=12, Criteria1:="c"

and copy this result to Sheet2 for next day processing in which the "c" will
be change to "m" so that these data can be include in the filter list when
the Merge
macro is run.

Your codes is superior to my current level of understanding in this subject
as I have just started to study and learn VBA, I was looking for
Worksheet.Add but couldn't
find it. Your effort and guidance is very much appreciate.

Many Thanks.

regards, francis

"Ron de Bruin" wrote:

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

Hi franciz

You can use this to add two empty worksheets

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet1"
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet2"

Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook.

Autofilter can have two options for each column

sourceRange.AutoFilter Field:=1, Criteria1:="=m", Operator:=xlOr, Criteria2:="=c"


--

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


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

I was having problem to respond as I was not able to access the reply page
after I click on reply, not sure why.

Thank for your assistance, your codes works excellently.
The reason for additional sheets is because I will copy a table in sheet1 of
another workbook, named "OLT" to a new sheet here. This is for a lookup on
customer names in Column C and its related rates in Column A of the "OLT"
workbook, so that VLookup can look at the table and search the customer names
in Column B and return the related rates in Column M of the active workbook,
named "Utility yyyy-mm-dd h-mm-ss"

Another option is to look at workbook "OLT" without copying the table to the
active
workbook, but not sure is this possible and should the other workbook be open?

What do you think? Your suggestion and input is very appreciate and valuable.

Another request is the the current macro will filter as :

sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
FilterField = 12
SearchValue = "m"

I have add the following filter :
sourceRange.AutoFilter Field:=3, Criteria1:="Y"
sourceRange.AutoFilter Field:=2, Criteria1:="<"

I need to also filter for Criteria1:="c" in Column L by adding this line

sourceRange.AutoFilter Field:=12, Criteria1:="c"

and copy this result to Sheet2 for next day processing in which the "c" will
be change to "m" so that these data can be include in the filter list when
the Merge
macro is run.

Your codes is superior to my current level of understanding in this subject
as I have just started to study and learn VBA, I was looking for
Worksheet.Add but couldn't
find it. Your effort and guidance is very much appreciate.

Many Thanks.

regards, francis

"Ron de Bruin" wrote:

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

Hi Ron,

Thanks for your assistance and valuable input.

I have not test the additional codes but will do it tomorrow and revert to
you on the outcome.

You mentioned that " Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook "

I am not too sure I understand this if for example, I use Vlookup in the
"Utility" workbook which is the workbook that shows the result of the filter
range, what other
formula do I need to use in the "OLT" workbook?

Pls advise, thanks

Regards, francis

"Ron de Bruin" wrote:

Hi franciz

You can use this to add two empty worksheets

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet1"
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet2"

Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook.

Autofilter can have two options for each column

sourceRange.AutoFilter Field:=1, Criteria1:="=m", Operator:=xlOr, Criteria2:="=c"


--

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


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

I was having problem to respond as I was not able to access the reply page
after I click on reply, not sure why.

Thank for your assistance, your codes works excellently.
The reason for additional sheets is because I will copy a table in sheet1 of
another workbook, named "OLT" to a new sheet here. This is for a lookup on
customer names in Column C and its related rates in Column A of the "OLT"
workbook, so that VLookup can look at the table and search the customer names
in Column B and return the related rates in Column M of the active workbook,
named "Utility yyyy-mm-dd h-mm-ss"

Another option is to look at workbook "OLT" without copying the table to the
active
workbook, but not sure is this possible and should the other workbook be open?

What do you think? Your suggestion and input is very appreciate and valuable.

Another request is the the current macro will filter as :

sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
FilterField = 12
SearchValue = "m"

I have add the following filter :
sourceRange.AutoFilter Field:=3, Criteria1:="Y"
sourceRange.AutoFilter Field:=2, Criteria1:="<"

I need to also filter for Criteria1:="c" in Column L by adding this line

sourceRange.AutoFilter Field:=12, Criteria1:="c"

and copy this result to Sheet2 for next day processing in which the "c" will
be change to "m" so that these data can be include in the filter list when
the Merge
macro is run.

Your codes is superior to my current level of understanding in this subject
as I have just started to study and learn VBA, I was looking for
Worksheet.Add but couldn't
find it. Your effort and guidance is very much appreciate.

Many Thanks.

regards, francis

"Ron de Bruin" wrote:

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

If your data table is in the OLT workbook

Your formula looks like this
=VLOOKUP(A1,'C:\Users\Ron\Desktop\[OLT.xls]Sheet1'!$A$1:$B$10,2,FALSE)

With both workbooks open enter the = sign and build the formula and use your mouse
to select the cell or data table.
After you close the OLT workbook it will automatic add the path before the workbook name in the formula.

Good luck


--

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


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

Thanks for your assistance and valuable input.

I have not test the additional codes but will do it tomorrow and revert to
you on the outcome.

You mentioned that " Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook "

I am not too sure I understand this if for example, I use Vlookup in the
"Utility" workbook which is the workbook that shows the result of the filter
range, what other
formula do I need to use in the "OLT" workbook?

Pls advise, thanks

Regards, francis

"Ron de Bruin" wrote:

Hi franciz

You can use this to add two empty worksheets

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet1"
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet2"

Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook.

Autofilter can have two options for each column

sourceRange.AutoFilter Field:=1, Criteria1:="=m", Operator:=xlOr, Criteria2:="=c"


--

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


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

I was having problem to respond as I was not able to access the reply page
after I click on reply, not sure why.

Thank for your assistance, your codes works excellently.
The reason for additional sheets is because I will copy a table in sheet1 of
another workbook, named "OLT" to a new sheet here. This is for a lookup on
customer names in Column C and its related rates in Column A of the "OLT"
workbook, so that VLookup can look at the table and search the customer names
in Column B and return the related rates in Column M of the active workbook,
named "Utility yyyy-mm-dd h-mm-ss"

Another option is to look at workbook "OLT" without copying the table to the
active
workbook, but not sure is this possible and should the other workbook be open?

What do you think? Your suggestion and input is very appreciate and valuable.

Another request is the the current macro will filter as :

sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
FilterField = 12
SearchValue = "m"

I have add the following filter :
sourceRange.AutoFilter Field:=3, Criteria1:="Y"
sourceRange.AutoFilter Field:=2, Criteria1:="<"

I need to also filter for Criteria1:="c" in Column L by adding this line

sourceRange.AutoFilter Field:=12, Criteria1:="c"

and copy this result to Sheet2 for next day processing in which the "c" will
be change to "m" so that these data can be include in the filter list when
the Merge
macro is run.

Your codes is superior to my current level of understanding in this subject
as I have just started to study and learn VBA, I was looking for
Worksheet.Add but couldn't
find it. Your effort and guidance is very much appreciate.

Many Thanks.

regards, francis

"Ron de Bruin" wrote:

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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

Merge all files in a folder
 

Hi Ron

I have insert this line :

sourceRange.AutoFilter Field:=12, Criteria1:="m", Operator:=xlOr,
Criteria2:="c"
With .AutoFilter.Range

under
sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
sourceRange.AutoFilter Field:=2, Criteria1:="<"


with the top having :

FilterField = 3
SearchValue = "Y"

the result doesn't show as expected, ie all the "m" with the other criteria
on the "Sheet1" and all the "c" on another sheet.

The result show all the "m" and "c" appear on sheet1.

Appreciate your guidance on where have I went wrong on this.

Thanks

regards, francis

"franciz" wrote:

Hi Ron,

Thanks for your assistance and valuable input.

I have not test the additional codes but will do it tomorrow and revert to
you on the outcome.

You mentioned that " Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook "

I am not too sure I understand this if for example, I use Vlookup in the
"Utility" workbook which is the workbook that shows the result of the filter
range, what other
formula do I need to use in the "OLT" workbook?

Pls advise, thanks

Regards, francis

"Ron de Bruin" wrote:

Hi franciz

You can use this to add two empty worksheets

Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet1"
BaseWks.Parent.Worksheets.Add.Name = "Yournewsheet2"

Lookup formulas will work with closed workbooks
If you create the formulas with both workbooks and save the file then it
will work without opening the OLT workbook.

Autofilter can have two options for each column

sourceRange.AutoFilter Field:=1, Criteria1:="=m", Operator:=xlOr, Criteria2:="=c"


--

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


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

I was having problem to respond as I was not able to access the reply page
after I click on reply, not sure why.

Thank for your assistance, your codes works excellently.
The reason for additional sheets is because I will copy a table in sheet1 of
another workbook, named "OLT" to a new sheet here. This is for a lookup on
customer names in Column C and its related rates in Column A of the "OLT"
workbook, so that VLookup can look at the table and search the customer names
in Column B and return the related rates in Column M of the active workbook,
named "Utility yyyy-mm-dd h-mm-ss"

Another option is to look at workbook "OLT" without copying the table to the
active
workbook, but not sure is this possible and should the other workbook be open?

What do you think? Your suggestion and input is very appreciate and valuable.

Another request is the the current macro will filter as :

sourceRange.AutoFilter Field:=FilterField, _
Criteria1:=SearchValue
FilterField = 12
SearchValue = "m"

I have add the following filter :
sourceRange.AutoFilter Field:=3, Criteria1:="Y"
sourceRange.AutoFilter Field:=2, Criteria1:="<"

I need to also filter for Criteria1:="c" in Column L by adding this line

sourceRange.AutoFilter Field:=12, Criteria1:="c"

and copy this result to Sheet2 for next day processing in which the "c" will
be change to "m" so that these data can be include in the filter list when
the Merge
macro is run.

Your codes is superior to my current level of understanding in this subject
as I have just started to study and learn VBA, I was looking for
Worksheet.Add but couldn't
find it. Your effort and guidance is very much appreciate.

Many Thanks.

regards, francis

"Ron de Bruin" wrote:

I was to fast

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

Also

If FNum = 1 Then
mybook.Worksheets(ShName).Range("A2:N2").Copy _
BaseWks.Range("B1")
BaseWks.Range("A1").Value = "WhatYouWant"
End If


How do I change this line of code to add the number of worksheet instead 1

Do you want to use your default amount of sheets when you open a new workbook or a different number
Why do you want to have empty sheets ?

--

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


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

Thank you very much for your help in this. It does do what I want except
that the
Headers of all the sourcefile does appear in the Sheet1's result on multiple
rows.

Is it possible to have the Header appear only once as the Header in the
result, ie Sheet1 of the new workbook on row 1?

Further, the current result show that the files' name start in A1, how can I
move it to start at A2 and name the Header column as Securities since the
first row is a Header row

How do I change this line of code to add the number of worksheet instead 1

'Add a new workbook with one sheet
Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
rnum = 1

Your help and guidance is very much appreciate

regards, francis



"Ron de Bruin" wrote:

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"