• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA help - Save error

stacey0240

New Member
Hi there. I'm new to VBA - someone else wrote this code, and I've been trying to tweak it. Unfortunately, I've done something and now it's not working.


When the Save button is clicked, it should copy & paste the data onto a log sheet, clear out the data, increase the PO Number by 1 (in cell C8), and save the file with a certain name. It does that filename just fine, and copies the data to the log perfectly. However, it doesn't do the other steps any more. Can someone tell me what's wrong please?

---------------------------

[pre]
Code:
Private Sub CommandButton1_Click() ' Dashboard
Range("A1").Select
Sheets("Dashboard").Select

End Sub
Private Sub CommandButton2_Click() ' "Save P/O"

Dim WSName As String, CName As String, Directory As String, savename As String
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, r2 As Range
Dim v1 As Variant, v2 As Variant

' Saving Order Only   ---------------------------------------------
strPath = ActiveWorkbook.Path

strName = Cells(8, 3).Text
Length = Len(strName)
If Length = 1 Then strName = "PO-00" + strName
If Length = 2 Then strName = "PO-0" + strName
strName = strName + "_" + Cells(8, 5).Text

strSave = strPath + "" + strName + " .xls"

Sheets("PurchaseOrder").Copy
ActiveSheet.Shapes("CommandButton1").Delete
ActiveSheet.Shapes("CommandButton2").Delete

On Error GoTo errorsub:
ActiveWorkbook.SaveAs Filename:=strSave

ActiveWorkbook.Close

' Log Purchase Order  --------------------------------------------------------
Set sh1 = Worksheets("PurchaseOrder")
Set sh2 = Worksheets("POLog")

' specify the cells that will contain information in sheet1
v1 = Array("c8", "e8", "g8", "c9", "e9", "g9", "d11", "d12", "d13", "d14", "c16")
' in the same order specify what columns that info would be
' placed in in sheet2
v2 = Array("a", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l")
' find the next open row in sheet2 using column A
rw = sh2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

' Now copy and clear the data
For i = LBound(v1) To UBound(v1)
Set r1 = sh1.Range(v1(i))
Set r2 = sh2.Cells(rw, v2(i))
r1.Copy r2
'  r1.ClearContents
Next i

' clears the value after it is transferred
Range("C9,E9,G9,D11:H14,C16:H16,B19:H33,G36:H36").ClearContents
Range("G8").Select

' Increase PO Number
With Sheets("PurchaseOrder").[C8]
.Value = .Value + 1
End With
ActiveWorkbook.Save
Exit Sub
errorsub:
Beep
MsgBox "Changes not saved!", vbExclamation, Title:=savename & ".xls"
End Sub
[/pre]
 
Hi ,


I hope you get your answer from someone else ; I can however help you find it out yourself.


The two most useful ways to troubleshoot macros within Excel , are to use the F8 key and the F9 key. Placing the cursor anywhere within your code and pressing the F8 key allows you to step through your code , one statement at a time. If you believe the problem is in the CommandButton2_Click() procedure , place your cursor anywhere within this procedure , and press F8. Keep pressing F8 to step through the code ; at any step , you can use the Immediate Window to view intermediate results.


Thus , say the following statement is highlighted when you press F8 :


strPath = ActiveWorkbook.Path


In the Immediate Window , you can type the following to get an answer :


?ActiveWorkbook.Path


If it gives an error , try and figure out why the answer is not what you would expect it to be.


Continuing in this fashion helps you to understand how your code "flows".


Narayan
 
Back
Top