Re: Merge all files in a folder

Tech-Archive recommends: Speed Up your PC by fixing your registry

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 *** 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
Work***.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 work*** 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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:8FF18D8E-D204-46C2-A5F7-4EC36313729A@xxxxxxxxxxxxxxxx
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 work*** instead 1

'Add a new workbook with one ***
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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:19C066AB-87B9-4914-9C54-9329F0C1F50F@xxxxxxxxxxxxxxxx
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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:691CC417-5359-4B1D-898C-3BA8B03CE5C2@xxxxxxxxxxxxxxxx
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" <franciz@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:C35FC453-3A81-4DCC-AD0B-076D8F04DED3@xxxxxxxxxxxxxxxx
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





.

Quantcast