Listing excel data forms into another excel book

[steppin16]

I'm using Excel 2003

I have done this once already, so I know its possible but I cannot remember what it is called and how to do it.

I have a "form", I open it up, fill out a few different things, and then save and print it. When I save it I want it to take a few different cells information and report it into a seperate workbook, without having to open the report workbook. I use this seperate workbook to reference when I fill out the form and who it was sent to.

I remember there was a wizard that I used to get it to do all this, but I can't for the life of me remeber what it was.

Please be easy I'm not a pro by any means.

Thanks
Jim

[Leith Ross]

Hello steppin16,

Correct me if I am wrong, but it seems that both workbooks are open at the same time. If that is the case then a simple macro can be added to copy those cells before the 'form" workbook is closed.

Sincerely,
Leith Ross

[steppin16]

No the "report" workbook won't be open. I only open the "form" workbook and when it is saved, it would write into the closed report workbook.


Thanks
Jim

[Leith Ross]

Hello steppin16,

This macro will run before the "form" workbook closes and copy the information into the other workbook automatically. The code marked in red will need to changed based on your needs.

Please Login or Register  to view this content.

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

[steppin16]

Well attempt 1 failed, but I think I know what I did wrong.

#1 what work book do I write the code on? The "form" or "report"?

#2 here is the code I wrote... can you show me better how the text needs to be formatted?

Please Login or Register  to view this content.

I really appreciate your help.
Jim

[Leith Ross]

Hello Jim,

The code should be placed in the "Form" workbook. AS for the formatting question, what directory is the other workbook in and what is its name? Which cells are being copied in the "Form"? What cells in the other workbook will they be copied to?

Sincerely,
Leith Ross

[steppin16]

the forms location
M:\Forms\Construction Forms\Transmittal form

the reports location
M:\Forms\Construction Forms\Transmittal Report Database

Copy from
Transmittal form Sheet2!F2:P2

paste to
Transmittal Report Database Sheet1!A2:K2


Thanks again
Jim

[Leith Ross]

Hello Jim,

I have added your information into the macro. Copy this code and install it in your workbook using the instructions below.

Please Login or Register  to view this content.

How to Save a Workbook Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on any Worksheet's Name Tab
3. Left Click on View Code in the pop up menu.
4. Press ALT+F11 keys to open the Visual Basic Editor.
5. Press CTRL+R keys to shift the focus to the Project Explorer Window
6. Press the Down Arrow Key until ThisWorkbook is highlighted in blue.
7. Press the Enter key to move the cursor to the Code Window
8. Paste the macro code using CTRL+V
9. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

[steppin16]

Leith,
Thanks so much for your help, its working. But I am having 2 issues,

1. Each time I open the "form" it has to paste to the next line down. Currently its writing it to the A2 line. (as I had told you) Is there any way to have it write to the next line down each time?
2. When I'm done I have the form, and attempt to close the file. It then asks me if I want to save the "report" and then asks if I want to save the "form" again. Is there any way to avoid this? My previous macro did not do this.

[Leith Ross]

HEllo steppin16,

Here is the amended macro code. It will automatically determine the next available row and not prompt you to save.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[steppin16]

Thank you so much.. It is still asking if I want to save, but I'll deal with it. The important thing is its working.

I just found what I had used before... Template Wizard with Data Tracking. It looks like they don't make it for excel 2003. Anyone know why didn't they update it after 2002?


Thanks Again Leith

Jim