CODEWITHSUNDEEP
vbaexcel
7A65726F
7A65726F

Reputation: 167

VBA - How to copy all data from other workbook and paste it in last row

I would like to copy the data from a workbook and paste it in the last row of the other workbook without the header.

For example:

Workbook1                                Workbook2
**Color** |  **Animals** |  Fruits       **Color** | **Animals**| **Fruits**               
yellow    | elephant     | apple         black     | pig        |  mango
blue      | girrafe      | banana        white     | dog        |  grapes

now what i want is all data from workbook will be pasted below worbk2 without the headers...

Workbook1
**Colors** | **Animal** | **Fruits**
yellow     | elephant   |  apple  
blue       | girrafe    |  banana
black      | pig        |  mango
white      | dog        |  grapes

can someone please help about this. heres my code..

Public Sub sample1()

Dim file1 As Excel.Workbook
Dim file2 As Excel.Workbook
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim CopyRange As Range

    'Open both workbooks first:
    Set file1 = Workbooks.Open(TextBox1.Text)
    Set file2 = Workbooks.Open(TextBox2.Text)

    Set Sheet1 = file1.Sheets(1)
    Set Sheet2 = file2.Sheets(1)

    lngLastCol = Sheet1.Cells(1, 256).End(xlToLeft).Column
    lngLastRow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row

    varArray = Sheet1.Range(Sheet1.Cells(2, 1), Sheet1.Cells(lngLastRow, lngLastCol)).Value

    Set CopyRange = Sheet1.Range("A1:A" & lngLastRow)
    CopyRange.EntireRow.Copy Sheet1.Cells(Rows.Count, 1).End(xlUp)(2)

    'Now, copy what you want from :
    file1.Sheets("sheet1").CopyRange.EntireRow.Copy Sheet1.Cells(Rows.Count, 1).End(xlUp)(2)


    'Now, paste to  worksheet:
    file2.Sheets("sheet1").Range("B1").PasteSpecial

    'Close 1-2:
    file2.Save
    file1.Close
    file2.Close

Upvotes: 1

Views: 621

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim rng As Range

Set Sheet1 = Workbooks.Open(TextBox1.Text).Sheets(1)
Set Sheet2 = Workbooks.Open(TextBox2.Text).Sheets(1)

Set rng = Sheet1.Range("A1").CurrentRegion 'assuming no blank rows/column
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, rng.Columns.Count) 'exclude header

Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize( _
              rng.Rows.Count, rng.Columns.Count).Value = rng.Value

Sheet2.Parent.Close True 'save changes
Sheet1.Parent.Close False 'don't save

Upvotes: 1

Related Questions