VBA copy data range from worksheets into new workbook

[newyears]

I have been searching the forum looking for an answer, but I can't find exactly what I'm looking for. I have issues trying to copy and index the paste. I appreciate any help

Here is what I'm trying to do:

User selects file that has multiple worksheets with different names. I want to copy rows 8 thru 15 from each worksheet into different workbook with sheet named "data" starting at row 1 and then pasting it. Data from next sheet (same file user selected) would be pasted below the first data set pasted -- all in workbook sheet named "data". It would loop thru each sheet and paste data below previous paste into workbook with sheet named "data".


Here is my code with copy and pasting...I used worksheet name "result" to copy one cell and paste in another workbook sheet "data". The user selected file has several different worksheet names (10 total), so I do not know how to loop and copy and paste advancing each paste.

    Dim Filter As String, Title As String
    Dim result As String
    Dim FilterIndex As Integer
    Dim wbsource As Workbook
        
    Dim wbdest As Workbook
    Set wbdest = ActiveWorkbook
        Dim Filename As Variant
        FFilter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
' Default Filter to *.*
FilterIndex = 3
Title = "Select a File to Open"
ChDrive ("c")
ChDir ("C:\Users\")
With Application
    ' ile Name to selected FileSet F
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    ' Reset Start Drive/Path
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
' Exit on Cancel
If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
Application.ScreenUpdating = False
    
wbdest.Unprotect
Set wbsource = Workbooks.Open(Filename)

wbsource.Sheets("result").Range("f8").Copy
wbdest.Sheets("data").Range("b4").PasteSpecial Paste:=xlPasteValues

 

[Fluff]

How about

Sub newyears()
   Dim Fname As String
   Dim Destws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   
   Set Destws = ThisWorkbook.Sheets("Data")
   ChDrive ("C:")
   ChDir ("C:\Users\")
   With Application
      Fname = .GetOpenFilename(, 3)
   End With
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets(1).Rows("8:15").Copy
   Destws.Range("A1").PasteSpecial xlPasteValues
   For i = 2 To Wbk.Sheets.Count
      Wbk.Sheets(i).Rows("8:15").Copy
      Destws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   Next i
   Wbk.Close False
End Sub

 

[newyears]

How about

Sub newyears()
   Dim Fname As String
   Dim Destws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   
   Set Destws = ThisWorkbook.Sheets("Data")
   ChDrive ("C:")
   ChDir ("C:\Users\")
   With Application
      Fname = .GetOpenFilename(, 3)
   End With
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets(1).Rows("8:15").Copy
   Destws.Range("A1").PasteSpecial xlPasteValues
   For i = 2 To Wbk.Sheets.Count
      Wbk.Sheets(i).Rows("8:15").Copy
      Destws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   Next i
   Wbk.Close False
End Sub

Hello....I appreciate the reply. I tried this and it copied first sheet and pasted in workbook "data" but failed when trying to paste second set of data range. I get the "method range of object _worksheet failed" on Destws.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

My assumption is the active sheet is the destination worksheet "data" and it does not know to back to original file and copy next worksheet.

 

[Fluff]

How about

Sub newyears()
   Dim Fname As String
   Dim Destws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   
   Application.ScreenUpdating = False
   Set Destws = ThisWorkbook.Sheets("Data")
   ChDrive ("C:")
   ChDir ("C:\Users\")
   With Application
      Fname = .GetOpenFilename(, 3)
   End With
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets(1).Rows("8:15").Copy
   Destws.Range("A1").PasteSpecial xlPasteValues
   For i = 2 To Wbk.Worksheets.Count
      Wbk.Worksheets(i).Rows("8:15").Copy
      Destws.Range("A" & Destws.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   Next i
   Application.CutCopyMode = False
   Wbk.Close False
End Sub

 

[newyears]

How about

Sub newyears()
   Dim Fname As String
   Dim Destws As Worksheet
   Dim Wbk As Workbook
   Dim i As Long
   
   Application.ScreenUpdating = False
   Set Destws = ThisWorkbook.Sheets("Data")
   ChDrive ("C:")
   ChDir ("C:\Users\")
   With Application
      Fname = .GetOpenFilename(, 3)
   End With
   Set Wbk = Workbooks.Open(Fname)
   Wbk.Sheets(1).Rows("8:15").Copy
   Destws.Range("A1").PasteSpecial xlPasteValues
   For i = 2 To Wbk.Worksheets.Count
      Wbk.Worksheets(i).Rows("8:15").Copy
      Destws.Range("A" & Destws.Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   Next i
   Application.CutCopyMode = False
   Wbk.Close False
End Sub

I really appreciate your help on this. It worked without error, but it pasted the data over the same rows instead of pasting below the first set of data, etc. I see the line for count rows and paste but it did not advance. It's probably simple and I'll search around for some answers.

 

[Fluff]

Is there any particular column that will always have a value in every cell?

 

[newyears]

Is there any particular column that will always have a value in every cell?

The worksheets each contain data from A to U and rows 8 to 15. I just need to paste this data from each worksheet into one workbook assuming the data from each sheet can be pasted subsequently below each one. There are only 6 to 8 worksheets to copy from the file. As I stated earlier, the worksheets have different names on them, but the data is same location on each.

Thank you for the help.

 

[Fluff]

If col A always has data in each of the 8 cells then the code will paste the next set of data under the previous set.

 

[newyears]

If col A always has data in each of the 8 cells then the code will paste the next set of data under the previous set.

Column A does not always have data, and once I entered data in column A for those rows it worked correctly. I'll have to think about how the data is entered on those work sheets.

Thank you for the help.

 

[Fluff]

As I said before

Is there any particular column that will always have a value in every cell?