Import Data Starting from next empty row

[Tanne]

Hi
I'm new to VBA and I'm trying to append 2 excel data files into the same worksheet.
I'm using the following coding for the 1st excel data file and it works great. I select the file path I want, data imports and the curser ends up on the next empty row.

**************************************
Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A6:S700").Copy
ThisWorkbook.Worksheets("Authorised Work").Range("A2").PasteSpecial xlPasteValues
OpenBook.Close False

End If
Application.ScreenUpdating = True

' Select Cell
Range("A1").End(xlDown).Offset(1).Select

End Sub
**************************************



I'm trying the use the same coding to import and append the 2nd excel data file, however I don't want to hard code the Range("A700") as this row changes daily depending on the number of data records that load each night.

***************************************
Sub Import_Data2()

' Import_Data2 Macro

Dim FileToOpen As Variant
Dim OpenBook As Workbook

Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A4:S300").Copy
ThisWorkbook.Worksheets("Authorised Work").Range("A700").PasteSpecial xlPasteValues
OpenBook.Close False

End If
Application.ScreenUpdating = True


End Sub
**********************************

I would like to import the 2nd excel data file from the next empty row which has been selected from the 1st lot of coding. This will always start in Column A but the row will always change. Instead of noting Range("A700) can this 1) be replace with some sort of ActiveCell Code or 2) be replaced with this code Range("A1").End(xlDown).Offset(1).Select within this line ThisWorkbook.Worksheets("Authorised Work").Range("A700").PasteSpecial xlPasteValues

Help Please I have no idea

 

[welshgasman]

Find the last used row in column A and increment by 1.
Why is this in the Access subforum?

 

[myall_blues]

Try this:

Sub Import_Data2()

' Import_Data2 Macro

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim NewRow As Long
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
    If FileToOpen <> False Then
        NewRow = Worksheets("Authorised Work").Range("A2").End(xlDown).Row + 1
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A4:S300").Copy
        ThisWorkbook.Worksheets("Authorised Work").Range("A" & NewRow).PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

As noted above, should be in Excel questions not Access.

 

[Tanne]

Find the last used row in column A and increment by 1.
Why is this in the Access subforum?

Find the last used row in column A and increment by 1.
Why is this in the Access subforum?

Sorry I'm also new to Mr Excel and finding my way around. Thank you for your advices too

 

[Tanne]

Try this:

Sub Import_Data2()

' Import_Data2 Macro

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim NewRow As Long
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files(*.xlsx*),*xlsx*")
    If FileToOpen <> False Then
        NewRow = Worksheets("Authorised Work").Range("A2").End(xlDown).Row + 1
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("A4:S300").Copy
        ThisWorkbook.Worksheets("Authorised Work").Range("A" & NewRow).PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

As noted above, should be in Excel questions not Access.

Thank you for the advice and apologies it's in the wrong area. I'm new to Mr Excel and finding my way around.

 

[myall_blues]

All good - as long as it works.

 

[Tanne]

All good - as long as it works.

I'll try tomorrow

 

[Tanne]

I'll try tomorrow

This worked - I'm so happy!!! Thank you