Hi Steve...
I appreciate your effort, I , from the developping country, Indonesia, feel
indebted to you all guys in the advanced countries. I have to admit that this
forum is very helpful to me and I can gain more lessons that we can apply in
our office works.

Greetings from Jakarta, Indonesia.

Frank

"steve_doc" wrote:

Hi Frank

Apologies for not posting sooner - just had a new project land on my work
table, so time is a little short.

I will do some digging and all being well will post some info for you over
the weekend.

Steve

"Frank Situmorang" wrote:

Hi Steve:

I learned from your last e-mail, I prefer to have a naming convention, I
appreciate if you could explain and give the example of VBA for:
1. Naming convention in case we want tio open and update 200 workbooks,
because what I want to do to extract the line items of Purchase Orders which
were typed in Excel. Each project has many P.O, typed downwardly.

2. Examaple of working with ranges, because the process is we will do
updating project by prject, mybe that must be a subroutine?. From all these
P.O's line item will be put into a workbook called " historical Unit price
based on P.O". This will be used by purchasing clerck to negotiate with
vendors.

So the ranges should be a dynamic range, because the number of line items
changes every time

Many thanks again,

Frank



"steve_doc" wrote:

To answer some of your questions in general terms
1. Opening many files in order to copy a range from the files
Yes
2. Copying the ranges to my file which I called a "data bank"
Yes

Due the variable nature of the file names as you stated, you could use an
User Prompt in your macro - but this requires that a user knows the exact
name of the file - same with the variable range.

Would it not be possible to implement a naming convention strategy?
eg. File name = abc1806.xls or DailyOrders1806.xls.

I am by no means saying that it is not possible, just that with variable
file names and ranges the chance of human error increases.

HTH

"Frank Situmorang" wrote:

Hello generous people:

Can we use macro for
1. Opening many files in order to copy a range from the files
2. Copying the ranges to my file which I called a "data bank"
Our purchase orders was typed in the excel, and the name of the files is the
name of the project. I want to take the line items to be used in my file in
order to know name of the materials and Unit price.
To be more details I explain what I do manually:
1. Open the file name abc.xls
2. copy from range a1....e50 to my data bank
3. Tommorrow there is an additional range in the same file abc.xls range
a51..e75, I copied it to my data bank file
4. the next day there is a project PQR, so there is a file for all P.O's in
this project named PQR.xls, I operned it and copy say range A1...E40.. to my
data bank.

my question is how can I create an VBA for this routine job. There are 2
things variable.

1. File name could increased
2. range is the file could change/variable.

I appreciate your idea on how to solve the problem. Clould it be a prompt
parameter to fill in the name of the file and the ranges? in the middle of
the process of macro when it is running?

Many thanks,

Frank