VBA - Loop through range, copy values one-by-one to other cell, save as CSV and continue

rowbro

Board Regular
Joined
Dec 16, 2010
Messages
50
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?

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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
As the thread name suggests, I am looking for a way to loop through a range of values in a sheet,
- Define the range, eg Column C, Range("B2:B100"), What?
selecting them one at a time and copying them to another cell,
- Spell out the destination cell, eg. same row, offset two columns to right or two rows up in column B. etc.
then saving a CSV before moving on to the next value in the range and doing the same again.
- This seems odd that only one cell is copied at a time and a new CSV file is then created, but not seeing the data, I can imagine that it might be allocating the files to different people or different locations based on what is in the copied range. Maybe you can elaborate a little on these issues.
 
Upvote 0
Thanks for the input. The range is in column D, and varies from sheet to sheet, so in one sheet it may be D5:D100, another could be D1:D5. Thus, we are pulling the range from the values in cells C5 and C6 (which will contain "D5" and "D100", respectively, if the range is D5:D100).

Each of the cells in the range need to be copied to cell C2, then the values in a different sheet (Sheet1) need to be saved as CSV. This file then gets uploaded to a database with a specific batch number.

This needs to repeat for the whole Dx:Dx range.

AS BACKGROUND: The values in the Dx range are dates, and the file is looking up values from tables and putting it into a specific format for upload to an online database. Each date range needs its own batch number, so that it can be easily changed, thus the individual CSV per date (or Dx range value).

Thanks again
 
Upvote 0
Well, I think I understand. Try this.

VBA Code:
Sub t()
Dim rng As Range, c As Range, fPath As String, fName As String
fPath = "\\172.16.2.2\FTPUPLOAD\STAGING_AREA\"
Set rng = Range(Range("C5").Value & ":" & Range("C6").Value)
    For Each c In rng
        If c <> "" Then
        c.Copy
        Range("C2").PasteSpecial xlPasteValues
        fName = "trade_figures" & Format(Range("C2").Value, "yyyymmdd")
        Sheets("Sheet1").Copy
        ActiveWorkbook.SaveAs fPath & fName & ".csv", FileFormat:=6, CreateBackup:=True, Local:=True
        ActiveWorkbook.Close
        End If
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top