View Full Version : [SOLVED:] Code To Save Worksheets As Individual .txt But Only The First 9 Columns Of Each sheet
jameswoods89
01-18-2017, 02:09 AM
Hi All,
I'm new to VBA, just started reading some books but looking for a bit of help in the meantime. I'm currently running the below code which saves all the worksheets in my workbook as individual files (in this case .txt). I'm wondering if there is a way to only save the first 9 columns from the left i.e. A to I.
Sub RenameTabs()
'Updateby20140624
For x = 1 To Sheets.Count
If Worksheets(x).Range("B3").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("B3").Value
End If
Next
End Sub
Any help would be greatly appreciated.
Many thanks,
James
Greetings and welcome to VBAX :hi:
Maybe you posted the wrong code?
Please use code tags when posting code. Like:
' Your code goes here
Mark
jameswoods89
01-18-2017, 02:33 AM
Hi Mark,
Thanks for that, apologies I posted the wrong code....forgive me it's quite late here.
The correct code is below
Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = CurDir & "\" & xWs.Name & ".txt"
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub
I was originally exporting the files at .CSV hence why the name says CSV still
James
p45cal
01-18-2017, 03:30 AM
You could add one line:
Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = CurDir & "\" & xWs.Name & ".txt"
ActiveSheet.Range(ActiveSheet.Range("J1"), ActiveSheet.Cells(1, ActiveSheet.Columns.Count)).EntireColumn.Clear '<< add this line.
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub
I haven't changed the rest because I don't know what you want to use as a delimiter.
A csv-file consisting of the first 9 columns:
Sub M_snb()
Sheet1.Cells(1).CurrentRegion.Resize(, 9).Copy
With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\saemple.csv").write .GetText
End With
End Sub
jameswoods89
01-18-2017, 04:52 AM
Thanks p45cal & snb I'll give both of these a try tomorrow and let you know. Appreciate you both taking the time!
James
jameswoods89
01-18-2017, 06:00 PM
Hi,
p45cal & snb just wanted to give you an update to let you know that both pieces of code work great!
Thank you so much for the help.
James
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.