Importing VBA code

[danielalvz]

Hello I am trying to modify a code that i got for importing into a excel file, this is the code that i currently have
Sub Get_Data_From_File()


Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim destLastRow As Long, sourceLastRow As Long

Application.ScreenUpdating = False

Set ws1 = ThisWorkbook.Sheets(2)

destLastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
If destLastRow < 18 Then destLastRow = 18

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)
Set ws2 = OpenBook.Sheets(2)

sourceLastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row
If sourceLastRow < 18 Then sourceLastRow = 18

ws2.Range("A18:D" & sourceLastRow).Copy
ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False

OpenBook.Close Savechanges:=False

End If

Application.ScreenUpdating = True

End Sub

This code is working well for me but i realize that i need it to import a few different ranges not just one, i need to import from "A18:D" which is currently written and works fine but i need to modify it to import also from F18:J R18:S and V18:S

 

[johnnyL]

First off, the ranges you mentioned that you want to add need to be corrected.
Second off, you didn't mention where you want those ranges to go to.

That being said:

Can't you replace:

ws2.Range("A18:D" & sourceLastRow).Copy
       ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       Application.CutCopyMode = False

with something like:

    ws2.Range("A18:D" & sourceLastRow).Copy
    ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("F18:J" & sourceLastRow).Copy
    ws1.Range("F" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("R18:S" & sourceLastRow).Copy
    ws1.Range("R" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("V18:S" & sourceLastRow).Copy
    ws1.Range("V" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'
    Application.CutCopyMode = False

Assuming you fix the ranges, that is.

 

[danielalvz]

First off, the ranges you mentioned that you want to add need to be corrected.
Second off, you didn't mention where you want those ranges to go to.

That being said:

Can't you replace:

ws2.Range("A18:D" & sourceLastRow).Copy
       ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       Application.CutCopyMode = False

with something like:

    ws2.Range("A18:D" & sourceLastRow).Copy
    ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("F18:J" & sourceLastRow).Copy
    ws1.Range("F" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("R18:S" & sourceLastRow).Copy
    ws1.Range("R" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("V18:S" & sourceLastRow).Copy
    ws1.Range("V" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'
    Application.CutCopyMode = False

Assuming you fix the ranges, that is.

you are a life saver man, it works like a charm many thanks

 

[danielalvz]

First off, the ranges you mentioned that you want to add need to be corrected.
Second off, you didn't mention where you want those ranges to go to.

That being said:

Can't you replace:

ws2.Range("A18:D" & sourceLastRow).Copy
       ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
       Application.CutCopyMode = False

with something like:

    ws2.Range("A18:D" & sourceLastRow).Copy
    ws1.Range("A" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("F18:J" & sourceLastRow).Copy
    ws1.Range("F" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("R18:S" & sourceLastRow).Copy
    ws1.Range("R" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ws2.Range("V18:S" & sourceLastRow).Copy
    ws1.Range("V" & destLastRow + 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
'
    Application.CutCopyMode = False

Assuming you fix the ranges, that is.

you are as life saver, this works like a charm many thanks

 

[johnnyL]

Glad to help!