So the current error I get is an End With without With
I've tried to add something that has "with" and then I get the error "For without Next"
and if I add a Next, then I get the error "Next without For"
What the heck am I doing wrong?
I'm trying to do the following tasks:
1. Create a folder with the name being a specific cell. That works great.
2. Save 4 sheets together as one workbook as an excel XML file. That works great.
3. Save one other workbook as a CSV. That is not perfect... when the CSV is saved, blank cells turn into "0" which messes up other stuff, so I tried to insert more code to replace columns A-Z with nothing if they have just a zero. That's where it all gets messed up.
Maybe someone knows what the heck is happening? Please, I'm out of ideas!
I've tried to add something that has "with" and then I get the error "For without Next"
and if I add a Next, then I get the error "Next without For"
What the heck am I doing wrong?
I'm trying to do the following tasks:
1. Create a folder with the name being a specific cell. That works great.
2. Save 4 sheets together as one workbook as an excel XML file. That works great.
3. Save one other workbook as a CSV. That is not perfect... when the CSV is saved, blank cells turn into "0" which messes up other stuff, so I tried to insert more code to replace columns A-Z with nothing if they have just a zero. That's where it all gets messed up.
Maybe someone knows what the heck is happening? Please, I'm out of ideas!
Code:
Option ExplicitSub TwoSheetsAndYourOut()
Dim NewName As String
Dim nm As Name
Dim ws As Worksheet
Dim FolderName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim xWs As Worksheet
Dim xWb As Workbook
Dim Rng As Range
Dim WorkRng As Range
Application.ScreenUpdating = False
Set xWb = Application.ThisWorkbook
FolderName = xWb.Path & "\" & Range("B3")
MkDir FolderName
If MsgBox("Export Sample Rule File" & vbCr & _
"Test Cases Will Also Be Created" _
, vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application
.ScreenUpdating = False
Sheets(Array("TransactionType", "REVERSAL", "SHIPPING + GIFT", "FORWARD NEW")).Copy
For Each ws In ActiveWorkbook.Worksheets
ws.Cells.Copy
ws.[A1].PasteSpecial Paste:=xlValues
ws.Cells.Hyperlinks.Delete
Application.CutCopyMode = False
Cells(1, 1).Select
ws.Activate
Next ws
Cells(1, 1).Select
NewName = InputBox("Please Specify the name of your new workbook, using the correct version number in '1-EU-ATINtoPTC-v?'", "New Copy")
ActiveWorkbook.SaveAs FolderName & "\" & NewName & ".xml", FileFormat:=xlXMLSpreadsheet, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = False
Sheets(Array("Test Cases")).Copy
Set WorkRng = Application.Selection
Set WorkRng = Application.Range("A1:Z30")
For Each Rng In WorkRng
If Rng.Value = 0 Then
Rng.Value = ""
On Error Resume Next
End If
NewName = InputBox("Please Specify the name of your test cases", "New Copy")
ActiveWorkbook.SaveAs FolderName & "\" & NewName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False
.ScreenUpdating = True
End With
Exit Sub
End Sub
