VBA to open workbook and copy selected cells

[StuartWhi]

Hi Guys,

After reviewing many threads, grabbing code from several unfortunately I’m still stuck and look forward to any suggestions.
- Used Excel for years and only now trying to learn VBA.

Running Excel 2007 on Vista.

What I would like to do,
- From an open file copy data from an excel file the user is prompted to open "ActiveWorkbook",
- ActiveWorkbook will have up to 6 WorkSheets to copy from, 1 x “Description” (always) and 5 x “Data” but the “Description” cells has to be added in front of the “Data” cells copied,
- Cells to be copied from both “Description” and “Data” worksheets are not consecutive or grouped thus I will have to manually code all these in (I will add this later).
- “Data” Worksheet name may vary (by the end users before submitting) and often not all are required, thus it would be good to check for any value in cell “E15” if yes process if not stop.
- Copy data into ThisWorkbook per row for each set of “Description”&”Data” worksheet (moving to the next row for the next copy and next workbook).
- At the end of each row place the Workbook & WorkSheet name.
I have included the below code as this addresses many areas but getting issues (I hope this will help you understand what I’m trying to do), look forward to any ideas on adjustment or totally new.

Thanks in advance and love the detail I can get from this website.

Option Explicit
Sub GetData()
Dim myFile As Workbook, sn As String, sn2 As String, NR As Long

Range("A1").Value = Application.GetSaveAsFilename()
myFile = ActiveWorkbook.Name

' Sheetname(s) to be copied
sn = Sheet6     '"Discription"
sn2 = Sheet2    '"Data_1"
'sn3 = Sheet5    '"Data_2"
'sn4 = Sheet12   '"Data_3"
'sn5 = sheet13   '"Data_4"
'sn6 = sheet14   '"Data_5"

    With ThisWorkbook.Sheets("Summary")
      NR = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      
      'D39, L34 from worksheet "Description"
      With .Range("A" & NR)
        .Formula = "='" & myFile & sn & "'!D39"
        .Value = .Value
      End With
      With .Range("B" & NR)
        .Formula = "='" & myFile & sn & "'!L34"
        .Value = .Value
      End With
      
      'F14, G14 from worksheet "Data_1"
      With .Range("D" & NR)
        .Formula = "='" & myFile & sn2 & "'!F14"
        .Value = .Value
      End With
      With .Range("E" & NR)
        .Formula = "='" & myFile & sn2 & "'!G14"
        .Value = .Value
        .Offset(, 1).Value = myFile
      End With
    End With
  
End Sub