Solved: Copy data from multiple worksheets in a workbook

[kathyb0527]

I am trying to pull data from multiple sheets in one source file to one sheet in a separate file. I would normally use vlookup but the destination sheet cannot contain formulas and one sheet in my source file doesn't have a common identifier to use vlookup. Some other factors that I need to consider are
1) When the source file is generated, the name will be different each time.
2) The number of rows may be different each time

I know I've seen similar code on the forum, but I'm struggling to figure out a way to adapt it. Any help would be appreciated!

[rbrhodes]

Hi kathy,

Could you post an example of what you have -stripped of senstive data (or at least a mocked up version of what you want?)

[kathyb0527]

I can only upload one file, but my destination table has these columns, and the data would start at row 14
Column A Seq. Number
Column B Sample Name
Column C Sample Kind
Column D Theorectical Conc (Calibration Conc on sheet 2 of source)
Column E Found Conc (Conc (ng/mL) on Sheet 1 of source)

There are other columns, but once you get me started, I should be able to continue it.

Thanks!

[mdmackillop]

Here's a starter with my best guess for column D
[vba]
Option Explicit
Sub GetData()
Dim WB As Workbook, Dest As Workbook, Rng As Range, Tgt As Range, Rws As Long
Dim c As Range, cel As Range

Set Dest = ThisWorkbook
Set WB = Workbooks(InputBox("Workbook name", , "Source_data") & ".xls")
With WB.Sheets(1)
Set Rng = Range(.Cells(3, 1), .Cells(3, 1).End(xlDown))
End With
Set Tgt = Dest.Sheets(1).Range("A14")
Rws = Rng.Cells.Count
Tgt.Resize(Rws).Value = Rng.Value
Tgt.Offset(, 1).Resize(Rws).Value = Rng.Offset(, 1).Value
Tgt.Offset(, 2).Resize(Rws).Value = Rng.Offset(, 6).Value
Tgt.Offset(, 4).Resize(Rws).Value = Rng.Offset(, 7).Value
With WB.Sheets(2)
'Add a helper column
.Range("I3").Resize(Rws).FormulaR1C1 = "=""Test number "" &RC[-8]&"" "" & RC[-7]"
For Each cel In Tgt.Offset(, 1).Resize(Rws)
Set c = .Columns(9).Find(What:=cel, lookat:=xlWhole, LookIn:=xlValues)
If Not c Is Nothing Then
cel.Offset(, 2) = c.Offset(, -6).Value
End If
Next
.Range("I3").Resize(Rws).ClearContents
End With
End Sub

[/vba]

[kathyb0527]

Works perfect, even the column "D"! Thank you so much!!

Kathyb0527