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

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 

rowbro

New Member
Joined
Dec 16, 2010
Messages
43
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
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,391
Messages
5,601,396
Members
414,448
Latest member
Jessica 22664

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
Top