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]
[/pre]
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