Can't copy cells to new workbook

[Lee Jeffery]

I am really, really new at this.
I am using Excel 97. I have a SAP application I use to export 2 column
of information to a table format in Excel. This information lands i
Columns A and B. Column A, rows 2 - 26 contains label information (e.g
first name, last name, date of birth, etc), Column B contains dat
relating to those labels (e.g. Lee, Jeffery, 19570604). Row 1 in bot
columns contains a header.

I don't want all of the information that SAP exports to Excel. I wan
specific information from Column B to copy and insert into a ne
workbook template saved on my C drive and called Daily_Work.xls. Thi
workbook is set up with a header row as follows: Column A: First Name
Column B: Last Name; Column C; Date of Birth.

I have tried to use the tip called Copy cells to all workbooks in
folder using VBA in Microsoft Excel but I don't get a result with thi
although I have saved the destination workbook into a folder calle
Data. The information I want from Column B is not in a broad range o
cells such as B2:B26. There are no values in some cells and other cell
contain information which is not wanted. The information I do want i
always located in the same source cells, however, it is possible tha
these cells may not always be populated with data.

Please assist with the process and code for performing this action
Should I be filtering info before trying to copy or can I nominat
specific cells for copying from and to to ensure the information i
displayed in the destination cells correctly? Also, the date forma
exported from SAP always shows as yyyymmdd. Please advise if I ca
amend this to a standard Excel date format.

I would be very grateful for any assistance as I am currently losin
many hours of productivity due to having to manually input the require
information to my destination file. Thank you

 

[BrianB]

Stop now and take a breather. There is a lot in your message about wha
you cannot do, but very little about what you are really trying t
achieve. That is what we and Excel need to know.

Before writing any macros you need to be sure that you can do the jo
manually. Every job has a start point and goes through various step
until the end. It is a good idea to write those steps down in the orde
that they need to be performed. Your final macro will be a replica o
what you decide.

Having made that decision it is time to automate the process. Copy al
your main files to a special folder which you are going to use fo
developing the macro so your originals are safe. Close everything dow
except for your starting file where you want the macro to live. Set th
macro recorder and go through your process manually exactly as you hav
written down. If transferring data manually, you do not have to do i
all, just enough that you have repeated the process a couple of times
Take your time. Stop the recorder at the end. You now have your whol
process written down in the right order.

Close your data results file *without saving changes*. With just you
starting workbook open again, run the macro and check that you get th
same results as the manual process.

You are now ready to edit the macro. The first thing you will probabl
want to do is to is automate the process of transferring all the dat
you require. Copy and paste just the code that does this from you
original into a new code module in the same book. Call it 'test sub(
or whatever. The aim is to produce a standalone macro that will only d
the transfer of data. So you will have your normal workbooks open, th
data will be imported and ready for transfer, and the macro when ru
will do this job alone. Save the workbook.

I sugest that you post this code into a new message (many of us do no
read messages with answers) together with information on how you choos
which data needs to be transferred. Focus on this alone to start with
because this is your main problem and should be something you can us
straight away. The scenario will be to go down the source data a row a
a time, check criteria, and transfer to the new sheet if required.

As time goes on you can deal with the rest of your process step by ste
in a similar way if problems occur.

To convert the date you will normally need another column, formatte
with a date format, with a formula like this :-
=DATEVALUE(CONCATENATE(RIGHT(A1,2),"/",MID(A1,5,2),"/",LEFT(A1,4))). W
can get the macro to do the conversion