Excel VBA Course
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

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: 36) 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