Is there a function by which we can paste data below existing data from another workbook

Hello! I am new to excel-vba programming. So basically Im trying to browse a file from the computer and COPY Data from that SELECTED File in Excel using vba, Im trying to select data from filled rows available in the selected excel workbook and paste it below existing data in the desired destination workbook here as "Masterfile.xlsm",

Can anyone PLEASE help me find errors here, suggestions to code in a better way are open.

Sub upload_data()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim wscopy As Worksheet
Dim wsdest As Worksheet
Dim crow As Long
Dim row As Long
Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
End If

Set wscopy = OpenBook.Worksheets(1)
Set wsdest = Workbooks("Masterfile.xlsm").Worksheets(1)

crow = wscopy.Cells(OpenBook.Rows.Count, "A").End(xlUp).row
row = wsdest.Cells(wsdest.Rows.Count, "A").End(xlUp).Offset(1).row

wscopy.Range("A2:J" & crow).copy
wsdest.Range("A" & row).PasteSpecial xlPasteValuesAndNumberFormats

Application.ScreenUpdating = True

End Sub

OHH Gosh! was stuck on it for days! its working perfectly okay!

Just one last question, if i want to add two cells as from A4:B4 and answer in C4 after uploading the file, should i create a module or do this in general section of the excel objects

Quote from Mumps

I'm not sure what you mean by the following:

Please clarify in detail step by step what you want to do referring to specific cells, ranges, sheets and workbooks.

SO bascially what i want to do it

- After uploading the data into Masterfile

- I want to calculate the percentage (Column I) and the formula would be " ColumnG/(ColumF - ColumnH)*100"

- I want this to calculate automatically once the data is uploaded and it should be calculated till the lastrow having the data

Quote from Mumps

Column I was included in the range that was copied from the file that was opened. If you insert the formula in column I, whatever is already in column I that was copied over will be replaced with the formula. Is that what you want to do?

I want the answer in Colum I. There isnt anything in Column I before or after the upload. Column I was not included in the range that was copied from the file that was opened.

Quote from Mumps

Just to note: Your original post included

Code

.Range("A2:J" & cRow).Copy

This range includes column I.

Do you want the formula to start in row 2 of column I ?

Apologies for the inconvenience, Actually I changed it, its now till columnH

.Range("A2:H" & cRow).Copy

and Yes i want to start from row2 thats where the data starts to filling in the copying procedure

Quote from Mumps

Just to note: Your original post included

Code

.Range("A2:J" & cRow).Copy

This range includes column I.

Do you want the formula to start in row 2 of column I ?

Formula is

ColumnI = ColumnG/(ColumF - ColumnH)*100

Quote from Mumps

Give this a try:

Code

Sub upload_data()
     Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long
     Set desWB = ThisWorkbook
     Set WSdest = desWB.Sheets(1)
     Application.ScreenUpdating = False
     FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
     If FileToOpen = False Then Exit Sub
     Set OpenBook = Application.Workbooks.Open(FileToOpen)
     With Sheets(1)
        cRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A2:J" & cRow).Copy
        With WSdest
            .Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            .Range("I2", .Range("I" & .Rows.Count).End(xlUp)).Formula = "= G2/(F2 - H2)*100"
        End With
     End With
     ActiveWorkbook.Close False
     Application.CutCopyMode = False
     Application.ScreenUpdating = True
End Sub

Display More

This is just working first 2 rows not until the last row having the data

Quote from Mumps

Are there any blank cells in column I? Can you attach a copy of the Master file after the data has been imported from the other file?

Yes Sure here is the copy of MasterFIle after the data has been imported

Masterfile.xlsm

Quote from Mumps

Try:

Code

Sub upload_data()
    Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long, lastRow As Long
    Set desWB = ThisWorkbook
    Set WSdest = desWB.Sheets(1)
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen = False Then Exit Sub
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
    With Sheets(1)
        cRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A4:H" & cRow).copy
        With WSdest
            lastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            .Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            .Range("I4:I" & lastRow).Formula = "= G4/(F4 - H4)"
        End With
    End With
    ActiveWorkbook.Close False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Display More

Sorry but its still the same! its only getting calculated for just firts 2 rows in ColumnI

Quote from Mumps

Give this a try:

Code

Sub upload_data()
     Dim WScopy As Worksheet, WSdest As Worksheet, desWB As Workbook, FileToOpen As Variant, cRow As Long
     Set desWB = ThisWorkbook
     Set WSdest = desWB.Sheets(1)
     Application.ScreenUpdating = False
     FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
     If FileToOpen = False Then Exit Sub
     Set OpenBook = Application.Workbooks.Open(FileToOpen)
     With Sheets(1)
        cRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("A2:J" & cRow).Copy
        With WSdest
            .Cells(WSdest.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats
            .Range("I2", .Range("I" & .Rows.Count).End(xlUp)).Formula = "= G2/(F2 - H2)*100"
        End With
     End With
     ActiveWorkbook.Close False
     Application.CutCopyMode = False
     Application.ScreenUpdating = True
End Sub

Display More

One thing i further noticed that its not working till the end row until I upload some data from either same or different workbook. As soon as the data is filled twice columnI is calculated for the previous uplaoded data