I'm trying to make an Excel VB macro that takes the incremental cells in each column and copies it incrementally to a new sheets, to later be exported as a csv for statistical analysis in spss or jamovi.
For example, if I have data in A1:A50 and B1:B50, I would like to copy A1:A(i) & B1:B(i) to worksheet(i), i = i + 1, then copy the new range A1:A(i) & B1:B(i) to a new worksheet.
Something like this? (Modified from Excel loop VBA Macro copy cells to new sheet and Exporting Data into a CSV - Excel VBA)
Dim Static As Excel.Worksheet
Dim Temp As Excel.Worksheet
Dim i As Integer
Set Static = ActiveWorkbook.Worksheets("WIP_List")
i = 1 'initial value of i
'Copies incremental range to new sheets
Do While IsEmpty(Static.Range("A" & i).Value) = False 'loops through
Set Temp = ActiveWorkbook.Worksheets("set (" & i & ")")
Static.Range("A1" & : & "A" & i).Copy _
Destination:=Temp.Cell("A1")
'This part exports the worksheet as a csv
Set ws = ThisWorkbook.Worksheets(i)
PathName = "" & ThisWorkbook.Path & "\" & ws.Name & ".csv"
ws.Copy
ActiveWorkbook.SaveAs Filename:=PathName, _
FileFormat:=xlCSV, CreateBackup:=False
i = i + 1
Loop
The idea is that if I have N number of samples, it will create N number of worksheets with i + 1 number of rows for each.
