Hi,
attached workbook Sheet 9,11,13 are passcord protected how to run macro module 5 in order to copy invoice (sheet 13) details to sheet 9 & 11
sheett 9,11,13 Password is "1234"
Thanks
Hi,
attached workbook Sheet 9,11,13 are passcord protected how to run macro module 5 in order to copy invoice (sheet 13) details to sheet 9 & 11
sheett 9,11,13 Password is "1234"
Thanks
Ramzein
You haven't really followed the recent separate advice from @Varaiatus but please try Module 5 in the attached file.
I have:
1) Moved all statements unlocking sheet protection to the start of Module 5 (and all re-apply protection statements to the end). You can "comment them out" and (working with unprotected sheets, perfect your code before enabling those stanements again (as Variatus suggested)
2) Commented out the sheet.activate statements- you don't really need them
3) Modified the formatting in Invoice Main (as I discussed in your earler question)
3) Removed merged cells from Invoice (but apply Wrap Text to address cell E23) so a mofified (single) statement can clear the necessary cells in that sheet.
Full code is below; see changed bits in bold. Again, I strongly recommend you work with unprotected code until everything is working well.
Sub Invtotal()
Dim ERow As Integer
Dim myPassword As String
'unlock sheets with same password
myPassword = "1234"
Sheet9.Unprotect Password:=myPassword
Sheet11.Unprotect Password:=myPassword
Sheet13.Unprotect Password:=myPassword
' Sheet9.Activate
ERow = Sheet9.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With Sheet9
.Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value
.Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value
.Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
.Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
.Cells(ERow, 5).Value = Day(.Cells(ERow, 4))
.Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4)))
.Cells(ERow, 7).Value = Year(.Cells(ERow, 4))
.Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
.Cells(ERow, 9).Value = Sheet13.Cells(45, 7).Value
.Cells(ERow, 10).Value = Sheet13.Cells(49, 9).Value
End With
Dim ItemCount As Integer
' Sheet11.Activate
ERow = Sheet11.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With Sheet11
For ItemCount = 34 To 43
If Sheet13.Cells(ItemCount, 4) <> "" Then
.Cells(ERow, 1).Value = Sheet13.Cells(22, 8).Value
.Cells(ERow, 2).Value = Sheet13.Cells(20, 5).Value
.Cells(ERow, 3).Value = Sheet13.Cells(20, 8).Value
.Cells(ERow, 4).Value = Sheet13.Cells(21, 8).Value
.Cells(ERow, 5).Value = Day(.Cells(ERow, 4))
.Cells(ERow, 6).Value = MonthName(Month(.Cells(ERow, 4)))
.Cells(ERow, 7).Value = Year(.Cells(ERow, 4))
.Cells(ERow, 8).Value = Sheet13.Cells(21, 5).Value
'add the item data
.Cells(ERow, 9).Value = Sheet13.Cells(ItemCount, 4).Value
.Cells(ERow, 10).Value = Sheet13.Cells(ItemCount, 5).Value
.Cells(ERow, 11).Value = Sheet13.Cells(ItemCount, 7).Value
.Cells(ERow, 12).Value = Sheet13.Cells(ItemCount, 8).Value
ERow = ERow + 1
Else
GoTo Finish
End If
Next ItemCount
End With
Finish:
'SavePDF Macro
'ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"D:\bills\" & Sheet13.Range("H22").Value, OpenAfterPublish:=True
MsgBox "Copied invoice details to " & Sheet13.Name & " and " & Sheet9.Name
' Clear_invoice Macro
' Sheet13.Activate
Sheet13.Range("E20:e23,H20:I20,c34:h43").ClearContents 'but not Amount column
'New Invoice.
Sheet13.Range("H22").Value = Sheet13.Range("H22").Value + 1
'protect sheets with same password
Sheet9.Protect Password:=myPassword, Contents:=True
Sheet11.Protect Password:=myPassword, Contents:=True
Sheet13.Protect Password:=myPassword, Contents:=True
End Sub
Sheet13.Activate
near the end of the macro (or uncomment the disabled one that's there in my Module 5) and you'll get there.