Scotteh
01-12-2016, 04:36 AM
Hi All,
First time on here and VBA virgin, currently trying to get my head around it.
I've searched the forum and come across a previous thread which I believe has my answer but, I cannot seem to get it to do what it should. It doesn't error, it just doesn't consolidate the sheets and I'm not sure why.
My aim is just to consolidate multiple sheets in one excel file into a single csv file with no spaces at the top. All the column names are the same in each sheet and I'd like a single set of headers present at the top of the consolidated file (taken from one of the sheets) and then all the data to be listed one after the other from the sheets below the headers.
Hope that makes sense!
Sub Consolidate()
With GetObject("Z:\Projects\Card Rec\Card_NEW.xlsm")
For Each sh In .Sheets
sn = sh.UsedRange.Offset(0, 0)
For j = 1 To UBound(sn) - 1
c00 = c00 & vbCrLf & Join(Application.Index(sn, j, 0), ",")
Next
Next
Close False
End With
CreateObject("scripting.filesystemobject").createtextfile("Z:\Projects\Card Rec\together.csv").write c00
End Sub
TIA, Scott
First time on here and VBA virgin, currently trying to get my head around it.
I've searched the forum and come across a previous thread which I believe has my answer but, I cannot seem to get it to do what it should. It doesn't error, it just doesn't consolidate the sheets and I'm not sure why.
My aim is just to consolidate multiple sheets in one excel file into a single csv file with no spaces at the top. All the column names are the same in each sheet and I'd like a single set of headers present at the top of the consolidated file (taken from one of the sheets) and then all the data to be listed one after the other from the sheets below the headers.
Hope that makes sense!
Sub Consolidate()
With GetObject("Z:\Projects\Card Rec\Card_NEW.xlsm")
For Each sh In .Sheets
sn = sh.UsedRange.Offset(0, 0)
For j = 1 To UBound(sn) - 1
c00 = c00 & vbCrLf & Join(Application.Index(sn, j, 0), ",")
Next
Next
Close False
End With
CreateObject("scripting.filesystemobject").createtextfile("Z:\Projects\Card Rec\together.csv").write c00
End Sub
TIA, Scott