copy specific cells from multiple files

0

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?

Answer
Discuss

Answers

0
Selected Answer

Kalil

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

destSheet.Cells.Clearcontents
(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
instead.

Hope this fixes things for you.

Discuss

Discussion

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