VBA Macro - Import data from different excel file for a given value

Hello guys,
I am very unprepared in everything that means VBA Macros but still I have to find a solution to make my work easier. :banghead:
If you don't mind, I'd like to share with you where I got stuck.

In one file I need some data from another file but not all the lines.

In the file where I have to get my data from, I have the following columns:

Code:

---

row_no.

task

user_id

upload_date

week_no.

year

asin

Marketplace

Keywords_for_upload

GL

PL

---

I would like, if possible, to import my lines for a value of the "week_no" column that I can add next to the button.

That's what I've managed to do so far, only I can't handle the condition for that "range":

Code:

---

Sub Get_Data_From_File()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
Application.ScreenUpdating = False
    FileToOpen = "C:\Source.xlsx"
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(1).Range("B1:K52").Copy
        ThisWorkbook.Worksheets(2).Range("A1").PasteSpecial xlPasteValues
        OpenBook.Close False
    End If
    Application.ScreenUpdating = True
End Sub

---

I hope I was quite explicit and thank you in advance for your help.

It's usually better to include sample attachments so people don't have to make some up

Use this as as starting point. My test data is attached

Code:

---

Option Explicit


Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim rData As Range
    Dim iRow As Long, iWeek As Long, iOut As Long


    FileToOpen = Application.GetOpenFilename("Data File (*.xlsx), *.xlsx")
    If FileToOpen = False Then Exit Sub
   
    iWeek = Application.InputBox("What Week?", "Get the Week Number", 0, , , , , 1)
    If iWeek < 1 Then Exit Sub
   
    Application.ScreenUpdating = False
   
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
   
    iOut = 2
   
    With OpenBook.Sheets(1)
        For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            If .Cells(iRow, 5).Value = iWeek Then
                .Cells(iRow, 1).Resize(1, 11).Copy ThisWorkbook.Sheets(1).Cells(iOut, 1)
                iOut = iOut + 1
            End If
        Next iRow
    End With
   
    OpenBook.Close False
   
    Application.ScreenUpdating = True
End Sub

---

Quote:

---

Originally Posted by Paul_Hossler

It's usually better to include sample attachments so people don't have to make some up

Use this as as starting point. My test data is attached

Code:

---

Option Explicit


Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Dim rData As Range
    Dim iRow As Long, iWeek As Long, iOut As Long


    FileToOpen = Application.GetOpenFilename("Data File (*.xlsx), *.xlsx")
    If FileToOpen = False Then Exit Sub
   
    iWeek = Application.InputBox("What Week?", "Get the Week Number", 0, , , , , 1)
    If iWeek < 1 Then Exit Sub
   
    Application.ScreenUpdating = False
   
    Set OpenBook = Application.Workbooks.Open(FileToOpen)
   
    iOut = 2
   
    With OpenBook.Sheets(1)
        For iRow = 2 To .Cells(1, 1).CurrentRegion.Rows.Count
            If .Cells(iRow, 5).Value = iWeek Then
                .Cells(iRow, 1).Resize(1, 11).Copy ThisWorkbook.Sheets(1).Cells(iOut, 1)
                iOut = iOut + 1
            End If
        Next iRow
    End With
   
    OpenBook.Close False
   
    Application.ScreenUpdating = True
End Sub

---

---

It works perfectly! Thanks for helping me!

A oneliner suffices

Code:

---

Sub M_snb()
  If Dir("C:\Source.xlsx")<>"" then Thisworkbook.sheets(1).cells(2,1).resize(52)=getobject("C:\Source.xlsx").sheets(1).range("B2:B52").offset(inputbox("weeknumber")-1).value
End Sub

---