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.
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.
Code:
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