Kullanıcı
- Katılım
- 30 Haz 2019
- Mesajlar
- 151
- Excel Versiyonu
- Excel 2016
- Excel Sürümü
- 64 Bit
- Excel Dili
- Türkçe
Merhaba,
Asagida ki macro yu calistirmak istedigimde sayfa korumali oldugundan hata aliyorum. Sayfa korumasini kaldirip macro calistirip geri kapatma ile ilgili yardimci olabilirmisiniz ?
Sub fatura()
Rows("20:24").AutoFit
Dim DateiName As String
Desktop = Environ("username")
yol = "K:\Fatura\"
DateiName = Range("O12") & (".pdf")
dosya_adi = yol & DateiName
ActiveSheet.Range("b2:k65").ExportAsFixedFormat Type:=xlTypePDF, Filename:=dosya_adi
Dim Outlook As Object
Dim OutlookMailItem As Object
Dim myAttachments As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set myAttachments = OutlookMailItem.Attachments
With OutlookMailItem
.To = Range("o13")
.cc = Range("o9")
.Subject = Range("o14")
.Body = Range("o15")
myAttachments.Add dosya_adi
.Display
End With
Set OutlookApp = Nothing
Set OutlookMailItem = Nothing
'Sheets("FT").Range("A" & x).Value = Sheets("Rechnung").Range("I9").Value
satir = Tabelle3.Cells(Tabelle3.Rows.Count, 1).End(3).Row + 1
vsutun = Array(9, 9, 9, 3, 9, 9, 9)
vsatir = Array(9, 8, 12, 8, 44, 45, 47)
For i = 0 To 6
Tabelle3.Cells(satir, i + 1) = Tabelle2.Cells(vsatir(i), vsutun(i))
Next i
veri = CSng(Split(Tabelle2.Cells(9, "i"), "-")(1) + 1)
If Len(veri) = 1 Then deger = "00": If Len(veri) = 2 Then deger = "0": If Len(veri) = 3 Then deger = ""
Tabelle2.Cells(9, "i") = Year(Date) & "-" & deger & veri
MsgBox "Fatura Kesilmistir. Islemler Saldenlist tablosuna aktarilmistir."
Sheets("Rechnung").Range("C22:H42").ClearContents
Sheets("Rechnung").Range("E17:J18").ClearContents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And Target.Row = 12 Then
For i = 2 To Tabelle1.Cells(Tabelle1.Rows.Count, 1).End(3).Row
If Tabelle1.Cells(i, 2) = Tabelle2.Cells(12, "L") Then
Tabelle2.Cells(8, "c") = Tabelle1.Cells(i, 2)
Tabelle2.Cells(9, "c") = Tabelle1.Cells(i, 3) & " " & Tabelle1.Cells(i, "d")
Tabelle2.Cells(10, "c") = Tabelle1.Cells(i, "e")
Tabelle2.Cells(11, "c") = Tabelle1.Cells(i, "f") & " " & Tabelle1.Cells(i, "g")
Tabelle2.Cells(17, "e") = Tabelle1.Cells(i, "l")
Tabelle2.Cells(18, "e") = Tabelle1.Cells(i, "m")
Tabelle2.Cells(22, "c") = Tabelle1.Cells(i, "o")
Tabelle2.Cells(22, "f") = Tabelle1.Cells(i, "p")
Tabelle2.Cells(22, "g") = Tabelle1.Cells(i, "q")
Tabelle2.Cells(22, "h") = Tabelle1.Cells(i, "r")
Tabelle2.Cells(23, "c") = Tabelle1.Cells(i, "t")
Tabelle2.Cells(23, "f") = Tabelle1.Cells(i, "u")
Tabelle2.Cells(23, "g") = Tabelle1.Cells(i, "v")
Tabelle2.Cells(23, "h") = Tabelle1.Cells(i, "w")
Tabelle2.Cells(24, "c") = Tabelle1.Cells(i, "y")
Tabelle2.Cells(24, "f") = Tabelle1.Cells(i, "z")
Tabelle2.Cells(24, "g") = Tabelle1.Cells(i, "aa")
Tabelle2.Cells(24, "h") = Tabelle1.Cells(i, "ab")
Tabelle2.Cells(25, "c") = Tabelle1.Cells(i, "ad")
Tabelle2.Cells(25, "f") = Tabelle1.Cells(i, "ae")
Tabelle2.Cells(25, "g") = Tabelle1.Cells(i, "af")
Tabelle2.Cells(25, "h") = Tabelle1.Cells(i, "ag")
Tabelle2.Cells(26, "c") = Tabelle1.Cells(i, "ai")
Tabelle2.Cells(26, "f") = Tabelle1.Cells(i, "aj")
Tabelle2.Cells(26, "g") = Tabelle1.Cells(i, "ak")
Tabelle2.Cells(26, "h") = Tabelle1.Cells(i, "al")
Tabelle2.Cells(27, "c") = Tabelle1.Cells(i, "an")
Tabelle2.Cells(27, "f") = Tabelle1.Cells(i, "ao")
Tabelle2.Cells(27, "g") = Tabelle1.Cells(i, "ap")
Tabelle2.Cells(27, "h") = Tabelle1.Cells(i, "aq")
Tabelle2.Cells(28, "c") = Tabelle1.Cells(i, "as")
Tabelle2.Cells(28, "f") = Tabelle1.Cells(i, "at")
Tabelle2.Cells(28, "g") = Tabelle1.Cells(i, "au")
Tabelle2.Cells(28, "h") = Tabelle1.Cells(i, "av")
Tabelle2.Cells(29, "c") = Tabelle1.Cells(i, "ax")
Tabelle2.Cells(29, "f") = Tabelle1.Cells(i, "ay")
Tabelle2.Cells(29, "g") = Tabelle1.Cells(i, "az")
Tabelle2.Cells(29, "h") = Tabelle1.Cells(i, "ba")
Tabelle2.Cells(30, "c") = Tabelle1.Cells(i, "bc")
Tabelle2.Cells(30, "f") = Tabelle1.Cells(i, "bd")
Tabelle2.Cells(30, "g") = Tabelle1.Cells(i, "be")
Tabelle2.Cells(30, "h") = Tabelle1.Cells(i, "bf")
Tabelle2.Cells(31, "c") = Tabelle1.Cells(i, "bh")
Tabelle2.Cells(31, "f") = Tabelle1.Cells(i, "bi")
Tabelle2.Cells(31, "g") = Tabelle1.Cells(i, "bj")
Tabelle2.Cells(31, "h") = Tabelle1.Cells(i, "bk")
Tabelle2.Cells(32, "c") = Tabelle1.Cells(i, "bm")
Tabelle2.Cells(32, "f") = Tabelle1.Cells(i, "bn")
Tabelle2.Cells(32, "g") = Tabelle1.Cells(i, "bo")
Tabelle2.Cells(32, "h") = Tabelle1.Cells(i, "bp")
Tabelle2.Cells(33, "c") = Tabelle1.Cells(i, "br")
Tabelle2.Cells(33, "f") = Tabelle1.Cells(i, "bs")
Tabelle2.Cells(33, "g") = Tabelle1.Cells(i, "bt")
Tabelle2.Cells(33, "h") = Tabelle1.Cells(i, "bu")
Tabelle2.Cells(34, "c") = Tabelle1.Cells(i, "bw")
Tabelle2.Cells(34, "f") = Tabelle1.Cells(i, "bx")
Tabelle2.Cells(34, "g") = Tabelle1.Cells(i, "by")
Tabelle2.Cells(34, "h") = Tabelle1.Cells(i, "bz")
Tabelle2.Cells(35, "c") = Tabelle1.Cells(i, "cb")
Tabelle2.Cells(35, "f") = Tabelle1.Cells(i, "cc")
Tabelle2.Cells(35, "g") = Tabelle1.Cells(i, "cd")
Tabelle2.Cells(35, "h") = Tabelle1.Cells(i, "ce")
Tabelle2.Cells(36, "c") = Tabelle1.Cells(i, "cg")
Tabelle2.Cells(36, "f") = Tabelle1.Cells(i, "ch")
Tabelle2.Cells(36, "g") = Tabelle1.Cells(i, "ci")
Tabelle2.Cells(36, "h") = Tabelle1.Cells(i, "cj")
Tabelle2.Cells(37, "c") = Tabelle1.Cells(i, "cl")
Tabelle2.Cells(37, "f") = Tabelle1.Cells(i, "cm")
Tabelle2.Cells(37, "g") = Tabelle1.Cells(i, "cn")
Tabelle2.Cells(37, "h") = Tabelle1.Cells(i, "co")
Tabelle2.Cells(38, "c") = Tabelle1.Cells(i, "cq")
Tabelle2.Cells(38, "f") = Tabelle1.Cells(i, "cr")
Tabelle2.Cells(38, "g") = Tabelle1.Cells(i, "cs")
Tabelle2.Cells(38, "h") = Tabelle1.Cells(i, "ct")
Tabelle2.Cells(39, "c") = Tabelle1.Cells(i, "cv")
Tabelle2.Cells(39, "f") = Tabelle1.Cells(i, "cw")
Tabelle2.Cells(39, "g") = Tabelle1.Cells(i, "cx")
Tabelle2.Cells(39, "h") = Tabelle1.Cells(i, "cy")
Tabelle2.Cells(40, "c") = Tabelle1.Cells(i, "da")
Tabelle2.Cells(40, "f") = Tabelle1.Cells(i, "db")
Tabelle2.Cells(40, "g") = Tabelle1.Cells(i, "dc")
Tabelle2.Cells(40, "h") = Tabelle1.Cells(i, "dd")
Tabelle2.Cells(41, "c") = Tabelle1.Cells(i, "df")
Tabelle2.Cells(41, "f") = Tabelle1.Cells(i, "dg")
Tabelle2.Cells(41, "g") = Tabelle1.Cells(i, "dh")
Tabelle2.Cells(41, "h") = Tabelle1.Cells(i, "di")
Tabelle2.Cells(42, "c") = Tabelle1.Cells(i, "dk")
Tabelle2.Cells(42, "f") = Tabelle1.Cells(i, "dl")
Tabelle2.Cells(42, "g") = Tabelle1.Cells(i, "dm")
Tabelle2.Cells(42, "h") = Tabelle1.Cells(i, "dn")
Tabelle2.Cells(10, "I") = Tabelle1.Cells(i, "I")
Sheets("Rechnung").Range("I8").Value = Now()
End If
Next i
End If
End Sub
Asagida ki macro yu calistirmak istedigimde sayfa korumali oldugundan hata aliyorum. Sayfa korumasini kaldirip macro calistirip geri kapatma ile ilgili yardimci olabilirmisiniz ?
Sub fatura()
Rows("20:24").AutoFit
Dim DateiName As String
Desktop = Environ("username")
yol = "K:\Fatura\"
DateiName = Range("O12") & (".pdf")
dosya_adi = yol & DateiName
ActiveSheet.Range("b2:k65").ExportAsFixedFormat Type:=xlTypePDF, Filename:=dosya_adi
Dim Outlook As Object
Dim OutlookMailItem As Object
Dim myAttachments As Object
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMailItem = OutlookApp.CreateItem(0)
Set myAttachments = OutlookMailItem.Attachments
With OutlookMailItem
.To = Range("o13")
.cc = Range("o9")
.Subject = Range("o14")
.Body = Range("o15")
myAttachments.Add dosya_adi
.Display
End With
Set OutlookApp = Nothing
Set OutlookMailItem = Nothing
'Sheets("FT").Range("A" & x).Value = Sheets("Rechnung").Range("I9").Value
satir = Tabelle3.Cells(Tabelle3.Rows.Count, 1).End(3).Row + 1
vsutun = Array(9, 9, 9, 3, 9, 9, 9)
vsatir = Array(9, 8, 12, 8, 44, 45, 47)
For i = 0 To 6
Tabelle3.Cells(satir, i + 1) = Tabelle2.Cells(vsatir(i), vsutun(i))
Next i
veri = CSng(Split(Tabelle2.Cells(9, "i"), "-")(1) + 1)
If Len(veri) = 1 Then deger = "00": If Len(veri) = 2 Then deger = "0": If Len(veri) = 3 Then deger = ""
Tabelle2.Cells(9, "i") = Year(Date) & "-" & deger & veri
MsgBox "Fatura Kesilmistir. Islemler Saldenlist tablosuna aktarilmistir."
Sheets("Rechnung").Range("C22:H42").ClearContents
Sheets("Rechnung").Range("E17:J18").ClearContents
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 12 And Target.Row = 12 Then
For i = 2 To Tabelle1.Cells(Tabelle1.Rows.Count, 1).End(3).Row
If Tabelle1.Cells(i, 2) = Tabelle2.Cells(12, "L") Then
Tabelle2.Cells(8, "c") = Tabelle1.Cells(i, 2)
Tabelle2.Cells(9, "c") = Tabelle1.Cells(i, 3) & " " & Tabelle1.Cells(i, "d")
Tabelle2.Cells(10, "c") = Tabelle1.Cells(i, "e")
Tabelle2.Cells(11, "c") = Tabelle1.Cells(i, "f") & " " & Tabelle1.Cells(i, "g")
Tabelle2.Cells(17, "e") = Tabelle1.Cells(i, "l")
Tabelle2.Cells(18, "e") = Tabelle1.Cells(i, "m")
Tabelle2.Cells(22, "c") = Tabelle1.Cells(i, "o")
Tabelle2.Cells(22, "f") = Tabelle1.Cells(i, "p")
Tabelle2.Cells(22, "g") = Tabelle1.Cells(i, "q")
Tabelle2.Cells(22, "h") = Tabelle1.Cells(i, "r")
Tabelle2.Cells(23, "c") = Tabelle1.Cells(i, "t")
Tabelle2.Cells(23, "f") = Tabelle1.Cells(i, "u")
Tabelle2.Cells(23, "g") = Tabelle1.Cells(i, "v")
Tabelle2.Cells(23, "h") = Tabelle1.Cells(i, "w")
Tabelle2.Cells(24, "c") = Tabelle1.Cells(i, "y")
Tabelle2.Cells(24, "f") = Tabelle1.Cells(i, "z")
Tabelle2.Cells(24, "g") = Tabelle1.Cells(i, "aa")
Tabelle2.Cells(24, "h") = Tabelle1.Cells(i, "ab")
Tabelle2.Cells(25, "c") = Tabelle1.Cells(i, "ad")
Tabelle2.Cells(25, "f") = Tabelle1.Cells(i, "ae")
Tabelle2.Cells(25, "g") = Tabelle1.Cells(i, "af")
Tabelle2.Cells(25, "h") = Tabelle1.Cells(i, "ag")
Tabelle2.Cells(26, "c") = Tabelle1.Cells(i, "ai")
Tabelle2.Cells(26, "f") = Tabelle1.Cells(i, "aj")
Tabelle2.Cells(26, "g") = Tabelle1.Cells(i, "ak")
Tabelle2.Cells(26, "h") = Tabelle1.Cells(i, "al")
Tabelle2.Cells(27, "c") = Tabelle1.Cells(i, "an")
Tabelle2.Cells(27, "f") = Tabelle1.Cells(i, "ao")
Tabelle2.Cells(27, "g") = Tabelle1.Cells(i, "ap")
Tabelle2.Cells(27, "h") = Tabelle1.Cells(i, "aq")
Tabelle2.Cells(28, "c") = Tabelle1.Cells(i, "as")
Tabelle2.Cells(28, "f") = Tabelle1.Cells(i, "at")
Tabelle2.Cells(28, "g") = Tabelle1.Cells(i, "au")
Tabelle2.Cells(28, "h") = Tabelle1.Cells(i, "av")
Tabelle2.Cells(29, "c") = Tabelle1.Cells(i, "ax")
Tabelle2.Cells(29, "f") = Tabelle1.Cells(i, "ay")
Tabelle2.Cells(29, "g") = Tabelle1.Cells(i, "az")
Tabelle2.Cells(29, "h") = Tabelle1.Cells(i, "ba")
Tabelle2.Cells(30, "c") = Tabelle1.Cells(i, "bc")
Tabelle2.Cells(30, "f") = Tabelle1.Cells(i, "bd")
Tabelle2.Cells(30, "g") = Tabelle1.Cells(i, "be")
Tabelle2.Cells(30, "h") = Tabelle1.Cells(i, "bf")
Tabelle2.Cells(31, "c") = Tabelle1.Cells(i, "bh")
Tabelle2.Cells(31, "f") = Tabelle1.Cells(i, "bi")
Tabelle2.Cells(31, "g") = Tabelle1.Cells(i, "bj")
Tabelle2.Cells(31, "h") = Tabelle1.Cells(i, "bk")
Tabelle2.Cells(32, "c") = Tabelle1.Cells(i, "bm")
Tabelle2.Cells(32, "f") = Tabelle1.Cells(i, "bn")
Tabelle2.Cells(32, "g") = Tabelle1.Cells(i, "bo")
Tabelle2.Cells(32, "h") = Tabelle1.Cells(i, "bp")
Tabelle2.Cells(33, "c") = Tabelle1.Cells(i, "br")
Tabelle2.Cells(33, "f") = Tabelle1.Cells(i, "bs")
Tabelle2.Cells(33, "g") = Tabelle1.Cells(i, "bt")
Tabelle2.Cells(33, "h") = Tabelle1.Cells(i, "bu")
Tabelle2.Cells(34, "c") = Tabelle1.Cells(i, "bw")
Tabelle2.Cells(34, "f") = Tabelle1.Cells(i, "bx")
Tabelle2.Cells(34, "g") = Tabelle1.Cells(i, "by")
Tabelle2.Cells(34, "h") = Tabelle1.Cells(i, "bz")
Tabelle2.Cells(35, "c") = Tabelle1.Cells(i, "cb")
Tabelle2.Cells(35, "f") = Tabelle1.Cells(i, "cc")
Tabelle2.Cells(35, "g") = Tabelle1.Cells(i, "cd")
Tabelle2.Cells(35, "h") = Tabelle1.Cells(i, "ce")
Tabelle2.Cells(36, "c") = Tabelle1.Cells(i, "cg")
Tabelle2.Cells(36, "f") = Tabelle1.Cells(i, "ch")
Tabelle2.Cells(36, "g") = Tabelle1.Cells(i, "ci")
Tabelle2.Cells(36, "h") = Tabelle1.Cells(i, "cj")
Tabelle2.Cells(37, "c") = Tabelle1.Cells(i, "cl")
Tabelle2.Cells(37, "f") = Tabelle1.Cells(i, "cm")
Tabelle2.Cells(37, "g") = Tabelle1.Cells(i, "cn")
Tabelle2.Cells(37, "h") = Tabelle1.Cells(i, "co")
Tabelle2.Cells(38, "c") = Tabelle1.Cells(i, "cq")
Tabelle2.Cells(38, "f") = Tabelle1.Cells(i, "cr")
Tabelle2.Cells(38, "g") = Tabelle1.Cells(i, "cs")
Tabelle2.Cells(38, "h") = Tabelle1.Cells(i, "ct")
Tabelle2.Cells(39, "c") = Tabelle1.Cells(i, "cv")
Tabelle2.Cells(39, "f") = Tabelle1.Cells(i, "cw")
Tabelle2.Cells(39, "g") = Tabelle1.Cells(i, "cx")
Tabelle2.Cells(39, "h") = Tabelle1.Cells(i, "cy")
Tabelle2.Cells(40, "c") = Tabelle1.Cells(i, "da")
Tabelle2.Cells(40, "f") = Tabelle1.Cells(i, "db")
Tabelle2.Cells(40, "g") = Tabelle1.Cells(i, "dc")
Tabelle2.Cells(40, "h") = Tabelle1.Cells(i, "dd")
Tabelle2.Cells(41, "c") = Tabelle1.Cells(i, "df")
Tabelle2.Cells(41, "f") = Tabelle1.Cells(i, "dg")
Tabelle2.Cells(41, "g") = Tabelle1.Cells(i, "dh")
Tabelle2.Cells(41, "h") = Tabelle1.Cells(i, "di")
Tabelle2.Cells(42, "c") = Tabelle1.Cells(i, "dk")
Tabelle2.Cells(42, "f") = Tabelle1.Cells(i, "dl")
Tabelle2.Cells(42, "g") = Tabelle1.Cells(i, "dm")
Tabelle2.Cells(42, "h") = Tabelle1.Cells(i, "dn")
Tabelle2.Cells(10, "I") = Tabelle1.Cells(i, "I")
Sheets("Rechnung").Range("I8").Value = Now()
End If
Next i
End If
End Sub