Trying to copy data from a specific sheet in one workbook to another with VBA

[druck21]

Hi everybody! I'm trying to create a macro that can take data from 2 different worksheets and combine it into one. I have everything working fine currently with one caveat, the data in both of the source workbooks needs to be in the 1st sheet, and I can't figure out how to change that.

One of the workbooks I need to pull from comes with the data I need on the 2nd sheet (named "Direct Payroll Adjustments") . I can of course go into that file and just remove the other sheets before I begin but that kind of defeats the purpose of having a macro. Here's a snippet of the code with the problem-

    Dim FileToOpen As Variant
    Dim OpenBook As Workbook

    Sheets.Add.Name = "Direct"
    
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your Direct Payroll Adjustments file", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
      Set OpenBook = Application.Workbooks.Open(FileToOpen)
         OpenBook.Sheets(1).Range("A1").Select
         Range(Selection, Selection.End(xlDown)).Select
         Range(Selection, Selection.End(xlToRight)).Select
         Selection.Copy
      ThisWorkbook.Worksheets("Direct").Range("A1").PasteSpecial xlPasteValues
      OpenBook.Close False

It's the line **"OpenBook.Sheets(1).Range("A1").Select"** that seems to be the problem. I've tried replacing the (1) with the name of the sheet in quotes, and a number 2(it's the 2nd sheet in the file) but neither work. I've also tried using OpenBook.Worksheets, that doesn't work either. Anyone know what I'm missing here? Thanks!

 

[MikeVol]

Hello @druck21.
Try next code:

    Dim TargetSheet As Worksheet
    Dim SourceSheet As Worksheet
    Sheets.Add.Name = "Direct"

    Dim FileToOpen  As Variant
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your Direct Payroll Adjustments file", FileFilter:="Excel Files (*.xls*),*xls*")

    If FileToOpen <> False Then

        Dim OpenBook As Workbook
        Set OpenBook = Application.Workbooks.Open(FileToOpen)

        On Error Resume Next
        Set TargetSheet = OpenBook.Sheets("Direct Payroll Adjustments")
        On Error GoTo 0

        If Not TargetSheet Is Nothing Then
            Set SourceSheet = TargetSheet
        Else
            Set SourceSheet = OpenBook.Sheets(1)
        End If

        ThisWorkbook.Worksheets("Direct").Range("A1").Resize(SourceSheet.Range("A1").CurrentRegion.Rows.Count _
                , SourceSheet.Range("A1").CurrentRegion.Columns.Count).Value = SourceSheet.Range("A1").CurrentRegion.Value
        OpenBook.Close False
    End If

    Set SourceSheet = Nothing
    Set OpenBook = Nothing

Maybe I misunderstood you, try it. Glad to help you. Good luck.

 

[druck21]

Worked perfectly, thank you so much!!