How can I export data from excel file to another excel file

Bujawad, Welcome.

Open Template and Database book.

In Template book Start Record New Macro.

Select and Copy A1:K1

View Database book

Select Cell A1

Press ctrl + down arrow... press down arrow one time more, should now be on next empty row

PasteSpecial Values

Back to Template book and stop record Macro.

Press ctrl+F11 to view new macro. Clean it up and give it a good name.

Come back here for more help.

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CUser%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:right; mso-pagination:widow-orphan; direction:rtl; unicode-bidi:embed; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0; mso-gutter-direction:rtl;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:159547206; mso-list-type:hybrid; mso-list-template-ids:61140040 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:39.2pt; mso-level-number-position:left; margin-left:39.2pt; text-indent:-18.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"جدول عادي"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

bujawad said:

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CUser%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:right; mso-pagination:widow-orphan; direction:rtl; unicode-bidi:embed; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0; mso-gutter-direction:rtl;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:159547206; mso-list-type:hybrid; mso-list-template-ids:61140040 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:39.2pt; mso-level-number-position:left; margin-left:39.2pt; text-indent:-18.0pt;} ol {margin-bottom:0cm;} ul {margin-bottom:0cm;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"جدول عادي"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]-->

<!--[endif]-->When I Press ctrl + down arrow. It will go to the end down of the sheet so I can't press down arrow one time more, and I am not on the next empty row.​

<!--[if !supportLists]-->​

Click to expand...

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 11">****** name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CUser%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; text-align:right; mso-pagination:widow-orphan; direction:rtl; unicode-bidi:embed; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} h3 {mso-margin-top-alt:auto; margin-right:0cm; mso-margin-bottom-alt:auto; margin-left:0cm; mso-pagination:widow-orphan; mso-outline-level:3; font-size:13.5pt; font-family:"Times New Roman";} span.authorvcard {mso-style-name:"author vcard";} @page Section1 {size:595.3pt 841.9pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0; mso-gutter-direction:rtl;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"جدول عادي"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi SamTYler

<o> </o>

Hi SamTYler again<o>
</o>

I managed to enter new data to new row, by modifying the code to the following:

Sub bujawad()

Range("A2:L2").Select<o></o>

Selection.Copy<o></o>

Windows("database.xls").Activate<o></o>

ER = Sheets("Database").Range("a5000").End(xlUp).Row + 1<o></o>

Sheets("Database").Range("A" & ER).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _<o></o>

:=False, Transpose:=False<o></o>

Application.CutCopyMode = False<o></o>

Windows ("Book1").Activate<o></o>

End Sub

But I have another two issues

<!--[if !supportLists]-->1.<!--[endif]-->If the database file was closed, I will try to reprogram the code to open it as I will keep the file in fixed location "C/"

<!--[if !supportLists]-->2.<!--[endif]-->If the form template have different name than "book1" as in the code, how can I program the code to recognize the file name/template name

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> bujawad()<br><SPAN style="color:#007F00">'Until completly automated:</SPAN><br>   <SPAN style="color:#007F00">'Always have database.xls open</SPAN><br>   <SPAN style="color:#007F00">'Always Have Template book active!</SPAN><br>   <SPAN style="color:#007F00">'Always run Macro from Template book!</SPAN><br>   <br><SPAN style="color:#007F00">'Note selections and activations not used because of</SPAN><br>   <SPAN style="color:#007F00">'direct references.</SPAN><br><br><SPAN style="color:#00007F">Dim</SPAN> Er <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> DBBook <SPAN style="color:#00007F">As</SPAN> Workbook<br>   <SPAN style="color:#007F00">'Set DBBook = (you can programmatically open database book here)</SPAN><br>   <SPAN style="color:#007F00">'Replace references to "[database.xls]" below with "DBBook"</SPAN><br>   <br><SPAN style="color:#00007F">Dim</SPAN> TemplateBook <SPAN style="color:#00007F">As</SPAN> Workbook<br>   <SPAN style="color:#007F00">'Set TemplateBook as needed</SPAN><br><br>Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#007F00">'TemplateBook.Range("A2:L2").Copy 'Use if completely Automated.</SPAN><br>   <SPAN style="color:#007F00">'Otherwise, with templatebook active, use next line.</SPAN><br>Range("A2:L2").Copy <SPAN style="color:#007F00">'Range is in Template book</SPAN><br><br><br>Er = [database.xls].Sheets("Database").Range("a5000").End(xlUp).Row + 1<br><br>[database.xls].Sheets("Database").Range("A" & Er).PasteSpecial _<br>           Paste:=xlPasteValues, Operation:=xlNone, _<br>           SkipBlanks:=False, Transpose:=False<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Dear SamTYler

thanks you very much for your fast respond, but its seems something wrong here and I can’t put my finger on it, therefore I attached template under file name Link with code included and Forger database for your review and advice

http://www.4shared.com/file/_7JiLV30/bujawad.html

on the other hand, i would like to request something else if you don't mind.

each form/template will have Special number. And this number will be registered in the column A.

my request is before pasting the data in the database I need the code to search if this number are available in the database “column A”
if yes Message will appear for two option (replace or Cancel )
if no data will be copied in the database

Buhawad,

Well, I finaly finished. I ran into some unusual problems that I am not sure how I fixed.

Here is a link to the new workbook, "DBUI.xls" that has a Sheet Form, "Control," that you can use.

http://www.4shared.com/file/DJjhxGsb/DBUI.html


One issue; You must remove the number formatting in column "A" of [Database.xls].Sheets("Database") or this will not work. idunnowyenot

When you open DBUI, a message, "you must open Database.xls..." will pop up. Close the message, but DO NOT OPEN database.xls at this time.

I included a Sheet, Database, to show you how it should look before sharing Database.xls with me or anyone else. After viewing it, delete sheet "Database."

YOU must edit the code as indicated by comments at the top of the "Control" sheet CodePage and the top of the "ThisWorkbook" CodePage, then save and close DBUI.xls, click ok, yes, or whatever to get thru all the warning popups.

These edits are because I wrote the code as if Column "K" was the last used column before the column in Database before the column you use to track the current location of Link.xls.

To Use: Copy the incoming data from Link.xls and PasteSpecial:= Values in Row 2, Column A. The code will do the rest. Enter the correct Link Tracking Code in Cell "B14" and the code will finish and prepare the Sheet Form for the next Link Entry.

If the entry is already in the database, Cell B12 will show "Existing, If the entry is new, it will show "New". In any case, enter the correct Link tracking code in "B14," and the code will replace the existing or add the new to Database.

If the entry exists, but there is a discrepancy, the discrepant Record in Database will show in Row 4 of the Sheet Form and the discrepant field will be highlighted RED. Whatever you do, when you enter a Tracking Code, the new entry will replace the existing entry in the database. To avoid this, simple enter the next record from the next Link.xls.

I forgot that issue until just now. If you look at the bottom of the AddRecord sub, you can figure out how to write a "Clear Entries" sub. Sorry about that.

My Dear friend …….YOUR the man,
I tried the magic in your file with the sample file and it was perfect, however I made no change inthe code, that because we are in the weekend “Thursday & Friday” so when I go back to the office in Saturday, I will make the changes in the actual file and will keep you posted
Will be in touch

The Grateful for your services
Bujawad