0

Just a quick question. I am trying to loop through 100 files in the same folder. Idea is to copy the value in one cell ("H9") and paste it into each subsequent row in a new workbook (wbf). I have attached the code I am working with but cant figure out how to go to next row.

      Do While myFile <> ""

      Set wb = Workbooks.Open(filename:=myPath & myFile)


      wb.Worksheets(1).Range("A10").Value = wbf.Worksheets(1).Range("A" & "start").Value

      myFile = Dir
  Loop

Thanks

pnuts
  • 58,317
  • 11
  • 87
  • 139
Navy Seal
  • 125
  • 1
  • 14

1 Answers1

0

You were almost on the right path, but to transfer value you to write something like :

Destination = Source

And to calculate the last used row, take a look at that post which detail what to use or not!

Here is how to do it :

Dim WsDest As Worksheet
Set WsDest = wbf.Worksheets(1)

myFile = Dir(Path)
Do While myFile <> ""

    Set Wb = Workbooks.Open(FileName:=myPath & myFile)
    WsDest.Range("A" & WsDest.Range("A" & WsDest.Rows.count).End(xlUp).Offset(1, 0).Row).Value = _
        Wb.Worksheets(1).Range("A10").Value
    myFile = Dir
Loop
Community
  • 1
  • 1
R3uK
  • 14,417
  • 7
  • 43
  • 77
  • Thanks R3uK, Ive been stuck on this for days. appreciate your help – Navy Seal Nov 23 '15 at 15:07
  • Did it solve your issue? If yes, please take a minute to take the tour and see how to validate an answer : http://stackoverflow.com/tour – R3uK Nov 23 '15 at 15:08