Hi,
As the thread name suggests, I am looking for a way to loop through a range of values in a sheet, selecting them one at a time and copying them to another cell, then saving a CSV before moving on to the next value in the range and doing the same again. My code below is extremely amateur (and needless to say, doesn't work). Can someone kindly assist?
As the thread name suggests, I am looking for a way to loop through a range of values in a sheet, selecting them one at a time and copying them to another cell, then saving a CSV before moving on to the next value in the range and doing the same again. My code below is extremely amateur (and needless to say, doesn't work). Can someone kindly assist?
VBA Code:
Sub LoopThrough()
'
' LoopThrough Macro
'
Dim rng As Range, cell As Range
Dim s1 As String, s2 As String
s1 = Range("C5"): s2 = Range("C6")
Set rng = Range(s1 & ":" & s2)
For Each cell In rng
Next cell
Range(s1).Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Dim strSourceSheet As String
Dim strFullname As String
strSourceSheet = "Sheet1"
strFullname = "\\172.16.2.2\FTPUPLOAD\STAGING_AREA\"
myfilenamedate = Format(Range("C2"), "yyyyMMdd")
myfilenameindicator = "trade_figures"
ThisWorkbook.Sheets(strSourceSheet).Copy
ActiveWorkbook.SaveAs Filename:=strFullname & myfilenameindicator & myfilenamedate & ".csv", _
FileFormat:=xlCSV, _
CreateBackup:=True, _
local:=True
ActiveWorkbook.Close
End Sub