Copy and Paste Between Files
 

Hi,

I have two excel files that are generated by an application. I have created
a third excel file that contains some macros/code. I am using these macros
to open these files one at a time and clean up some of the headers etc and
delete some columns. My goal is to take rows from one sheet and paste them
into the other sheet at a cetain location.

In the following example, I need to take IT people from File1 and paste them
into File2 -BEFORE- S.Smith in IT and after AC department. Same way, I need
to copy all HR people from File1 and paste them into File2 Before Q. Johnson
in HR and After IT people. I have been able to find correct rows and use the
'cut' command but paste is not available when I switch to File 2. Any ideas?
Also, when I try to close either of the files I am getting Error 400 or
something similar.

Thank you..

Example:
File 1

Name Dept. Location
J. Smith IT CA
K. Jones IT NY
S. Moor HR CA
P. Johnson HR CA
P. Smith HR CA

File 2

Name Dept. Location
K. Jones AC NY
S. Smith IT CA
W. Smith IT CA
Q. Johnson HR CA
R. Smith HR CA

Result should be:

File 2
Name Dept. Location
K. Jones AC NY
J. Smith IT CA
K. Jones IT NY
S. Smith IT CA
W. Smith IT CA
S. Moor HR CA
P. Johnson HR CA
P. Smith HR CA
Q. Johnson HR CA
R. Smith HR CA

Copy and Paste Between Files
 

As long as you know the logic of which rows to cut/copy and where to paste:

Dim WB1 As Workbook
Dim WB2 As Workbook

Set WB1 = Workbooks.Open("C:\File1.xls")
Set WB2 = Workbooks.Open("C:\File2.xls")

WB1.Worksheets(1).Range("A6:C6").Cut 'or Copy ?
WB2.Worksheets(1).Range("A6").Insert Shift:=xlDown 'Correct direction ?

WB1.Close False 'Or True ?
WB2.Close False 'Or True ?

By the way, why not just add to the end of the list and then Sort as
required ?
(Although ACITHR is not listed alphabetically)

NickHK


"Dragon" wrote in message
...
Hi,

I have two excel files that are generated by an application. I have
created
a third excel file that contains some macros/code. I am using these macros
to open these files one at a time and clean up some of the headers etc and
delete some columns. My goal is to take rows from one sheet and paste them
into the other sheet at a cetain location.

In the following example, I need to take IT people from File1 and paste
them
into File2 -BEFORE- S.Smith in IT and after AC department. Same way, I
need
to copy all HR people from File1 and paste them into File2 Before Q.
Johnson
in HR and After IT people. I have been able to find correct rows and use
the
'cut' command but paste is not available when I switch to File 2. Any
ideas?
Also, when I try to close either of the files I am getting Error 400 or
something similar.

Thank you..

Example:
File 1

Name Dept. Location
J. Smith IT CA
K. Jones IT NY
S. Moor HR CA
P. Johnson HR CA
P. Smith HR CA

File 2

Name Dept. Location
K. Jones AC NY
S. Smith IT CA
W. Smith IT CA
Q. Johnson HR CA
R. Smith HR CA

Result should be:

File 2
Name Dept. Location
K. Jones AC NY
J. Smith IT CA
K. Jones IT NY
S. Smith IT CA
W. Smith IT CA
S. Moor HR CA
P. Johnson HR CA
P. Smith HR CA
Q. Johnson HR CA
R. Smith HR CA