Copy Rows from one File to Another

Hello
This assumes both files 1 and 2 are open, with sheettabs in each file the same name as the file  File1 and File2.
Looking for some VBA that would copy source file (File1) to target file (File2)

File1 has headers so all I want is rows under the headers (row1), even if there is only one row of data.  There will always be at least one.

Copy them

Append to File2, even if all that is in File2 is the headers (row1).  If there is data in File2, append to the bottom.  if there is data in File2 already there will always be data in columnA.  
.
I do not have sample workbook, but I believe this is pretty straight forward.  I seem to have issues when there is only one row to copy and when I do an xlDown it grabs all.
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can do something like this...

I have assumed you want to copy data from Book2 from A Column to I Column..including row-1 from sheet-1 of book2 to the sheet1 of the workbook from where you are running this code..

Sub copydata()
Dim wb As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim lr As Long, lr1 As Long
Set wb = ThisWorkbook
Set wb1 = Workbooks("Book2.xlsx")

Set ws = wb.Sheets("Sheet1")
Set ws1 = wb1.Sheets("Sheet1")

lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
lr1 = ws1.Cells(Cells.Rows.Count, "a").End(xlUp).Row

ws1.Range("A1:I" & lr1).Copy ws.Range("A" & lr)
End Sub

Open in new window


Saurabh...
RWayneHAuthor Commented:
Actual I am running this from a external control macro book, so the wb = ThisWorkbook will not work.  Thinking about this all I do with the "assuming the two files are open" is open the two with VBA.  Is there a way to do this in the background?   Have wb = a filepath?  and wb1 also?  Grab however many rows (even if it is one row) from file1, instead of copy, CUT them and append to File2.  File2 could only have headers in row 1 or 100's of rows.

Also what is Ln15?  Range("A1:I" is that setting the columns to cut?  I need to select the whole row no matter the number of columns used..  Let me know and thanks.
Saurabh Singh TeotiaCommented:
You can use something like this...

set wb=workbooks.open("Your file path\Your file name")

similarly for wb1 which will open your workbook...

Now you can do a copy command and then you can do a clear command which will work as cut.. Also yeah I is the number of columns i have assumed that you want to copy...from A Column...
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

RWayneHAuthor Commented:
So I will have to extend the columns out to column BB in Ln15, and what is the command to close the files after?  This way it will open the files, run the append and then close them  (Saving the changes made to both back to there original paths).  CUT instead of COPY please.  Could you rewrite the suggested code to include the items discussed?  Thanks.
Saurabh Singh TeotiaCommented:
You can do something like this...

Sub copydata()
Dim wb As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim lr As Long, lr1 As Long
Set wb = Workbooks.Open("Your path here\Filename")
Set wb1 = Workbooks.Open("Your path here\Filename")

Set ws = wb.Sheets("Sheet1")
Set ws1 = wb1.Sheets("Sheet1")

lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
lr1 = ws1.Cells(Cells.Rows.Count, "a").End(xlUp).Row



ws1.Range("A1:BB" & lr1).Cut ws.Range("A" & lr)

wb.Close (True)
wb1.Close (False) ' Assuming you want to save changes here..

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
Ok... testing is not going well

For some reason when going to wb1, it is grabbing the header row too, we only want what is under that, even if it is just one row.  All testing is bringing the header row each time.  What edit would we make to the follow code that would stop it from including the header row in wb1?  Below is the code I am testing with.  Please advise and thanks.

Sub AppendSOLnInfoForMultiOrders()
Dim wb As Workbook
Dim wb1 As Workbook
Dim ws As Worksheet, ws1 As Worksheet
Dim lr As Long, lr1 As Long
Set wb = Workbooks.Open("\\nahollap548\HP-UFT\ScriptResourceFiles\SOLNDataAppendList.xls")
Set wb1 = Workbooks.Open("\\nahollap548\HP-UFT\ScriptResourceFiles\ProcessOrTestingResults.xls")

Set ws = wb.Sheets("SOLNData")
Set ws1 = wb1.Sheets("SOLNData")

lr = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
lr1 = ws1.Cells(Cells.Rows.Count, "a").End(xlUp).Row

ws1.Range("A1:BB" & lr1).Cut ws.Range("A" & lr)

wb.Close (True)
wb1.Close (True) ' Assuming you want to save changes here..  was False
End Sub

Open in new window

RWayneHAuthor Commented:
It was Ln15, need to edit A1 to A2:BB...  working now.. but doing some more testing.  Thanks, it looks like this will work!!!  thanks for the help.
Saurabh Singh TeotiaCommented:
Okay yeah u need to start from row-2 in that case..let me know if you need any further help..
RWayneHAuthor Commented:
Testing was successful, thanks for the help with this.  One last question regarding this.  How would this react to having autofilters on when running this?  Would the rows be deleted with shifting up?  would it leave blank the rows?  would it work at all?
Saurabh Singh TeotiaCommented:
You are using cut..even those rows will be copied and will be hidden even after copy...
RWayneHAuthor Commented:
I will have to run a test when there is a autofilter applied to the data to see... sounds like a yes the rows will be gone after the cut/paste.  This is fine... but with an autofilter cut/paste, how does effect the in-between rows.
Saurabh Singh TeotiaCommented:
It will move them as well..or you can add a code of line to remove autofilter which will be like this...

ws1.autofilter

This will remove autofilter applied in sheet..
RWayneHAuthor Commented:
Let me see if I understand this correctly..  If the source file is autofiltered, it will cut out those rows, (leaving it blank) and send them to the target file.  Then if I return to the source file and use ws1.autofilter (which will remove the autofilter). I will still have a contiguous set of data in the source file, and another with a subset of the source.   This is like a bonus!!!  If I am understanding this right.
Saurabh Singh TeotiaCommented:
No if you are applying filter..it will copy data with rows it will copy the rows and will move it to the newfile and when you paste their you will see those rows hidden as its as you doing cut..however if you just want to move non filter rows you want to copy them pastespecial and then you can clear the data from your source file.. and then you can come back and remove filter..
RWayneHAuthor Commented:
Ok understood, still a bonus it be able to use it with autofilter.  Thanks!!
RWayneHAuthor Commented:
Excellent!!!  Thanks for the help with this.
Saurabh Singh TeotiaCommented:
Yw..Always happy to help..

Saurabh...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.