hi
I need helping to fix this problem . every thing is ok with this code except one thing . when import the data from multiple files into summary file . it deletes the headers in summary file despite I specify the data after headers
the headers in file summary starts from row3 , the data begins from row 4 as the code should copy from row4 .
Public Sub Copy_Values_From_Workbooks()
Dim matchWorkbooks As String
Dim destSheet As Worksheet, r As Long
Dim folderPath As String
Dim wbFileName As String
Dim fromWorkbook As Workbook
'Folder path and wildcard workbook files to import cells from
matchWorkbooks = "D:\path\*.xlsx" 'CHANGE THIS
'Define destination sheet
Set destSheet = ActiveWorkbook.Worksheets("Summary") 'CHANGE THIS
destSheet.Cells.Clearcontents
r = 0
Application.ScreenUpdating = False
folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
wbFileName = Dir(matchWorkbooks)
While wbFileName <> vbNullString
Set fromWorkbook = Workbooks.Open(folderPath & wbFileName)
With fromWorkbook.Worksheets(1)
destSheet.Range("B4").Offset(r).Value = .Range("B4").Value
destSheet.Range("C4").Offset(r).Value = .Range("H4:H5").Value
destSheet.Range("E4").Offset(r).Value = .Range("H19").Value
r = r + 1
End With
fromWorkbook.Close savechanges:=False
DoEvents
wbFileName = Dir
Wend
Application.ScreenUpdating = True
MsgBox "Finished"
End Sub
I beleive this line
destSheet.Cells.ClearContents
should mod , but how? I still search for it.
any help please?