Loop through range. Use range data as input

[cemvula]

I am struggling to add further functionality to my code. I would like for my code to run a loop through a range of dates, found in column "E:E", and thereby use this input into cell C2 and then perform the rest of my code - saving the file to CSV (SaveAsCSV()). The spreadsheet is such that it already xlookups information in various sheets to "Sheet1" it just requires that the date, in C2 found on my "input" sheet, be amended.

Sub SaveAsCSV()
Dim strSourceSheet As String
Dim strFullname As String



strSourceSheet = "Sheet1"
strFullname = "\\H:\Database\Upload Files\"
myfilenamedate = Format(Range("C2"), "yyyyMMdd")
myfilenameindicator = "x"

ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname & myfilenameindicator & myfilenamedate & ".csv", _
                      FileFormat:=xlCSV, _
                      CreateBackup:=True, _
                      local:=True
ActiveWorkbook.Close

End Sub

 

[DanteAmor]

How about:

Sub SaveAsCSV()
  Dim strSourceSheet As String
  Dim strFullname As String
  Dim myfilenamedate As String
  Dim myfilenameindicator As String
  Dim sh As Worksheet
  Dim i As Long
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh = Sheets("Input")
  strSourceSheet = "Sheet1"
  strFullname = "\\H:\Database\Upload Files\"
  For i = 2 To sh.Range("E" & Rows.Count).End(3).Row
    sh.Range("C2").Value = sh.Range("E" & i).Value
    myfilenamedate = Format(sh.Range("C2"), "yyyyMMdd")
    myfilenameindicator = "x"
    
    ThisWorkbook.Sheets(strSourceSheet).Copy
    ActiveWorkbook.SaveAs Filename:=strFullname & myfilenameindicator & myfilenamedate & ".csv", _
      FileFormat:=xlCSV, CreateBackup:=True, local:=True
    ActiveWorkbook.Close False
  Next
  
  Application.ScreenUpdating = True
End Sub

 

[cemvula]

How about:

Sub SaveAsCSV()
  Dim strSourceSheet As String
  Dim strFullname As String
  Dim myfilenamedate As String
  Dim myfilenameindicator As String
  Dim sh As Worksheet
  Dim i As Long
 
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
 
  Set sh = Sheets("Input")
  strSourceSheet = "Sheet1"
  strFullname = "\\H:\Database\Upload Files\"
  For i = 2 To sh.Range("E" & Rows.Count).End(3).Row
    sh.Range("C2").Value = sh.Range("E" & i).Value
    myfilenamedate = Format(sh.Range("C2"), "yyyyMMdd")
    myfilenameindicator = "x"
   
    ThisWorkbook.Sheets(strSourceSheet).Copy
    ActiveWorkbook.SaveAs Filename:=strFullname & myfilenameindicator & myfilenamedate & ".csv", _
      FileFormat:=xlCSV, CreateBackup:=True, local:=True
    ActiveWorkbook.Close False
  Next
 
  Application.ScreenUpdating = True
End Sub

Will attempt it this evening and will let you know how it goes. Thanks for the help.