VBA nested loop getting stuck at copying a cell

[quiellx]

I made the following VBA code, the idea is to first loop through an entire folder with CSV files, and in each one of those inserts, in cell A1, a value coming from a "master" file, from which the macro is being run. The code looks like this.

Sub AllWorkbooks()
Dim MyFolder As String 'Path collected from the folder picker dialog
Dim MyFile As String 'Filename obtained by DIR function
Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder & "*.csv", vbNormal) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
Do While MyFile <> ""
'Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
'Replace the line below with the statements you would want your macro to perform
Windows("master.xlsm").Activate
Dim c As Range
For Each c In ActiveSheet.Range("A1:A2000")
wbk.Activate
Sheets(1).Range("a1").Value = c
wbk.Close savechanges:=True
Exit For
Next
MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub

I followed the step-by-step process of the macro and the issue seems to come from this block
Windows("master.xlsm").Activate
Dim c As Range
For Each c In ActiveSheet.Range("A1:A2000")
wbk.Activate
Sheets(1).Range("a1").Value = c
wbk.Close savechanges:=True
Exit For
Next

For some reason, the macro is just copying the first value of the master file in all the CSV files... It most likely is something easy to solve but I have very little knowledge of VBA and I can't find a way to make it work.
Thanks for any suggestions or help.

 

[offthelip]

Your problem is that you have two loops where you only need one, try this modification, this increments the value picked up in C everytime you write are file out:

Sub AllWorkbooks()
Dim MyFolder As String 'Path collected from the folder picker dialog
Dim c As Variant     ' add this line
Dim rowno As Integer ' add this line

Dim MyFile As String 'Filename obtained by DIR function
Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder & "*.csv", vbNormal) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
rowno = 1   ' add this line
Do While MyFile <> ""
'Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
'Replace the line below with the statements you would want your macro to perform
Windows("master.xlsm").Activate
c = Range(Cells(rowno, 1), Cells(rowno, 1)) ' add this line
wbk.Activate
Sheets(1).Range("a1").Value = c
rowno = rowno + 1                     ' add this line
wbk.Close savechanges:=True

MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub

Note when posting code please use the code tags by clicking on the vba symbol , this formats the code

 

[quiellx]

Your problem is that you have two loops where you only need one, try this modification, this increments the value picked up in C everytime you write are file out:

Sub AllWorkbooks()
Dim MyFolder As String 'Path collected from the folder picker dialog
Dim c As Variant     ' add this line
Dim rowno As Integer ' add this line

Dim MyFile As String 'Filename obtained by DIR function
Dim wbk As Workbook 'Used to loop through each workbook
On Error Resume Next
Application.ScreenUpdating = False
'Opens the folder picker dialog to allow user selection
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then 'If no folder is selected, abort
MsgBox "You did not select a folder"
Exit Sub
End If
MyFolder = .SelectedItems(1) & "\" 'Assign selected folder to MyFolder
End With
MyFile = Dir(MyFolder & "*.csv", vbNormal) 'DIR gets the first file of the folder
'Loop through all files in a folder until DIR cannot find anymore
rowno = 1   ' add this line
Do While MyFile <> ""
'Opens the file and assigns to the wbk variable for future use
Set wbk = Workbooks.Open(Filename:=MyFolder & MyFile)
'Replace the line below with the statements you would want your macro to perform
Windows("master.xlsm").Activate
c = Range(Cells(rowno, 1), Cells(rowno, 1)) ' add this line
wbk.Activate
Sheets(1).Range("a1").Value = c
rowno = rowno + 1                     ' add this line
wbk.Close savechanges:=True

MyFile = Dir 'DIR gets the next file in the folder
Loop
Application.ScreenUpdating = True
End Sub

Note when posting code please use the code tags by clicking on the vba symbol , this formats the code

Worked perfectly, thanks so much.. i'm quite new in programming and even more with VBA. Thanks again for taking the time to correct it.