Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

copy specific cells from multiple files



 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

    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
        wbFileName = Dir

    Application.ScreenUpdating = True

    MsgBox "Finished"

End Sub

I  beleive  this  line   

 should  mod  , but  how?  I  still  search  for  it.

any help please?



Selected Answer


Assuming you have nothing in rows 1 and 2 of your summary sheet, I suggest you change the line:

(which will delete everything on the sheet) to read:
destSheet.UsedRange.Offset(1, 0).Clearcontents
which will delete everything apart from the first row(3) which contains your headers.

If you have data in row 1 though but data starting in row 4, use

destSheet.UsedRange.Offset(3, 0).Clearcontents

Hope this fixes things for you.



it  works  excellently . thanks  so  much !
Kalil (rep: 22) Nov 23, '21 at 6:47 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login