Need help with this VBA code

[Rafea1979]

Hi

I need VBA code which will copy one value from excel files then paste all values with the name of the file taken from in one master excel file.

Example on what I need

File name Value
Book1 1.2
Book2 1.5
Book3 1.6

I have VBA codes to copy the values however I do NOT know how to copy and paste the file names! I need help to find the code to copy the file name next to its value.

Thank you

[Rafea1979]

Here is the code

Sub LoopAllExcelFilesInFolder()

'PURPOSE: To loop through all Excel files in a user specified folder and perform a set task on them

Dim wb As Workbook
Dim myPath As String
Dim myFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog
Dim erow

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual
'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings


'Target File Extension (must include wildcard "*")
  myExtension = "*.xls"

'Target Path with Ending Extention
  myFile = Dir(myPath & myExtension)

i = 1
    
'Loop through each Excel file in folder
  Do While myFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(Filename:=myPath & myFile)
    
    'Copy D-spacing C4 cell
      wb.Worksheets(1).Range("C4:C4").Copy
       
    'Close Activeworkbook
      ActiveWorkbook.Close
    
    ' Paste C4 into another master excel file
       erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row + i
       ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range(Cells(erow, 1), Cells(erow, 1))
    
        i = i + 1
        
    'Save and Close Workbook
    ' wb.Close SaveChanges:=True

    'Get next file name
      myFile = Dir
  Loop


'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

[SamT]

Try replacing the loop in that code with this loop

     'Loop through each Excel file in folder
    Do While myFile <> ""
         'Set variable equal to opened workbook
        Set wb = Workbooks.Open(Filename:=myPath & myFile)
         
        'Place values in Columns A and B of first empty Row
        With Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
          .Offset(1, 0).Value = wb.Name
          .Offset(1, 1).Value = wb.Worksheets(1).Range("C4").Value
        End With
         
         'Close Opened Workbook
          wb.Close SaveChanges:=True
         
         'Get next file name
        myFile = Dir
    Loop

[Rafea1979]

Thank you however I did what you have suggested and still getting the below errors

Sorry I'm NOT a programmer but I have been trying to create this VBA code to analyze some data.

Error message1.jpgError message2.jpg

[SamT]

First, make sure that you have a worksheet named "Sheet1"

Then try replacing the "A" after Rows.Count with 1

(Replace "A" with quotes, with a 1 without quotes.)

BTW, aren't you going to close the "wb" workbook when you are done with it and before you open a new one?

You really should, or you will have dozens of open workbooks.

[Rafea1979]

Thank you very much for the help however I have two issues
1. All excel files I had do NOT have sheet1 so I should change all into sheet1
2. It worked after I have changed few files to sheet1 but it copied the C4 and file name in the same file.

I really thank you for your help but How can I extract the value into a separate excel file having only values and file names.

Thank you

[SamT]

It is very important in VBA, (and in all other codes,) to say exactly what you mean.

For example: Sheet1, Worksheets(1), and Worksheets("Sheet1") mean three different things. I can give you a workbook that has three Worksheets in it and use each of those terms to reference a different sheet. OR they can all reference the same sheet. You must understand your Workbook to understand which term to use to access which sheet you wish.

The workbook with the code in it must have a Worksheet with the Tab Name "Sheet1"

All the Workbooks with the Values in them must have only one Worksheet in them, in order for this code to work as is. It does not matter what the Tab Name of the Worksheet is if there is only one Worksheet. That is why the Code is using Worksheets(1) for the Value sheet.





Attachment 15065


The so called Tab Name is the name of the sheet as seen on the sheet tabs in Excel.

Note in the image that I have the name Sheet1 in two places! One is the Code Name of the Worksheet ("Current Summary"). The Other is the Tab Name of the (Sheet) Object Sheet2. Since I have many Worksheets (Sheets), If I use Sheets(1) in my code, I don't know which sheet I will get.

Sheets and Worksheets are two different things, but all Worksheets are also in Sheets. Not all Sheets are in Worksheets. (There are also Sheets called Charts, which are not Worksheets.)

[Rafea1979]

This is an excellent explanation.

I really appreciate your time and help.

It works with me.

Sweet

[SamT]

Does that mean that your code is working now?

You can use the "Thread Tools" menu above the posts to mark this thread "Solved."

[Rafea1979]

Yes
it is working fine and also I have learned much more about VBA.

[SamT]