Changing the cell referenced in a loop in VBA

[mickerlad]

Hi Folks

I'm wondering is it possible in VBA to write a piece of code which will keep the formula the same, but change the cell it is referencing each time it loops through?

For example, I have a sheet and wish to create a macro that will:
- Open a .csv file from a list of files in the input tab
- Copy and paste the contents into a new tab in the original spreadsheet
- Rename the tab to the name of the .csv file
- close the .csv file then move onto the next file in the list and repeat until all files are pasted in

I have the following code:

Sub Test1()

Dim x As Integer
' Set numrows = number of rows of data.
NumRows = Range("B11", Range("B11").End(xlDown)).Rows.Count
' Select cell.
Range("B11").Select
' Establish "For" loop to loop "numrows" number of times.
For x = 1 To NumRows
'Create New Tab
Sheets.Add After:=Sheets(Sheets.Count)
'Rename the tab
ActiveSheet.Name = Sheets("Sheet1").Range("B11").Value
'Opens the csv file
Dim CSV_FILE As Workbook
Set CSV_FILE = Workbooks.Open(Filename:="(filepath of csv files)" & "\" & Sheets("Sheet1").Range("B7").Value & "\" & Sheets("Sheet1").Range("B11").Value & ".csv")
'Copy all values in sheet
ActiveSheet.Cells.Select
Selection.Copy
'Paste values into new tab & clear clipboard
Windows("Macro.xlsm").ActiveSheet.Paste
Application.CutCopyMode = False
'Close spreadsheet
CSV_FILE.Close

'Selects cell down 1 row from active cell.
Range("B11").Select
ActiveCell.Offset(1, 0).Select

Next

End Sub


The value in cell 'B7' is the date which will change monthly. The list of .csv files starts at cell 'B11' in 'Sheet1' and these will be the same month on month.

So I would want the 'B11' to change to 'B12' after the first tab is created, then paste the data from the .csv referenced in 'B12', then 'B12' change to 'B13' etc and keep looping through until all the .csv files listed are pasted into new tabs.

Any help would be greatly appreciated. Thanks in advance!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

Adding an Offset to the range should work.

Sub Test1()

    Dim x As Integer
    ' Set numrows = number of rows of data.
    NumRows = Range("B11", Range("B11").End(xlDown)).Rows.Count
    ' Select cell.
    Range("B11").Select
    ' Establish "For" loop to loop "numrows" number of times.
    
    For x = 1 To NumRows
        'Create New Tab
        Sheets.Add After:=Sheets(Sheets.Count)
        'Rename the tab
        ActiveSheet.Name = Sheets("Sheet1").Range("B11").Offset(x - 1).Value
        'Opens the csv file
        Dim CSV_FILE As Workbook
        Set CSV_FILE = Workbooks.Open(Filename:="(filepath of csv files)" & "\" & Sheets("Sheet1").Range("B7").Value & "\" & Sheets("Sheet1").Range("B11").Offset(x - 1).Value & ".csv")
        'Copy all values in sheet
        ActiveSheet.Cells.Select
        Selection.Copy
        'Paste values into new tab & clear clipboard
        Windows("Macro.xlsm").ActiveSheet.Paste
        Application.CutCopyMode = False
        'Close spreadsheet
        CSV_FILE.Close
        
        'Selects cell down 1 row from active cell.
        Range("B11").Offset(x - 1).Select
        ActiveCell.Offset(1, 0).Select
    Next

End Sub

Please use the CODE tags to post code. All you need to do is select your code then hit the "#" button, thanks.
Also, indenting it will reveal the "shape" of the code more easily.

 

[mickerlad]

Hi Rick

Thanks that worked perfectly. I'll keep in mind the code tags and indentation in future!

Thanks!

 

[RickXL]

No problem, thanks for letting me know.

Regards,