Aktarma da kısaltma

[aydgur]

Sub sonerolaktarma299()
Sheets("ANASAYFA").Select
For x = 2 To [A65536].End(3).Row
Set s2 = Sheets(Cells(x, 1).Text)
sira = s2.[A65536].End(3).Row + 1
Select Case s2.Name
Case "STOK"

s2.Cells(sira, 1) = Cells(x, "A")
s2.Cells(sira, 2) = Cells(x, "B")
s2.Cells(sira, 3) = Cells(x, "C")
s2.Cells(sira, 4) = Cells(x, "D")
s2.Cells(sira, 5) = Cells(x, "E")
s2.Cells(sira, 6) = Cells(x, "F")
s2.Cells(sira, 7) = Cells(x, "G")
s2.Cells(sira, 8) = Cells(x, "H")
s2.Cells(sira, 9) = Cells(x, "I")
s2.Cells(sira, 10) = Cells(x, "J")
s2.Cells(sira, 11) = Cells(x, "K")
s2.Cells(sira, 12) = Cells(x, "L")
s2.Cells(sira, 13) = Cells(x, "M")
s2.Cells(sira, 14) = Cells(x, "N")
s2.Cells(sira, 15) = Cells(x, "O")
s2.Cells(sira, 16) = Cells(x, "P")
s2.Cells(sira, 17) = Cells(x, "Q")
s2.Cells(sira, 18) = Cells(x, "R")
s2.Cells(sira, 19) = Cells(x, "S")
s2.Cells(sira, 20) = Cells(x, "T")
s2.Cells(sira, 21) = Cells(x, "U")
s2.Cells(sira, 22) = Cells(x, "V")
s2.Cells(sira, 23) = Cells(x, "W")
s2.Cells(sira, 24) = Cells(x, "X")
s2.Cells(sira, 25) = Cells(x, "Y")
s2.Cells(sira, 26) = Cells(x, "Z")
s2.Cells(sira, 27) = Cells(x, "AA")
s2.Cells(sira, 28) = Cells(x, "AB")
s2.Cells(sira, 29) = Cells(x, "AC")
s2.Cells(sira, 30) = Cells(x, "AD")
s2.Cells(sira, 31) = Cells(x, "AE")
s2.Cells(sira, 32) = Cells(x, "AF")
s2.Cells(sira, 33) = Cells(x, "AG")
s2.Cells(sira, 34) = Cells(x, "AH")
s2.Cells(sira, 35) = Cells(x, "AI")
s2.Cells(sira, 36) = Cells(x, "AJ")
s2.Cells(sira, 37) = Cells(x, "AK")
s2.Cells(sira, 38) = Cells(x, "AL")
s2.Cells(sira, 39) = Cells(x, "AM")
s2.Cells(sira, 40) = Cells(x, "AN")
s2.Cells(sira, 41) = Cells(x, "AO")
s2.Cells(sira, 42) = Cells(x, "AP")
s2.Cells(sira, 43) = Cells(x, "AQ")
s2.Cells(sira, 44) = Cells(x, "AR")
s2.Cells(sira, 45) = Cells(x, "AS")
s2.Cells(sira, 46) = Cells(x, "AT")
s2.Cells(sira, 47) = Cells(x, "AU")
s2.Cells(sira, 48) = Cells(x, "AV")
s2.Cells(sira, 49) = Cells(x, "AW")
s2.Cells(sira, 50) = Cells(x, "AX")
s2.Cells(sira, 51) = Cells(x, "AY")
s2.Cells(sira, 52) = Cells(x, "AZ")
s2.Cells(sira, 53) = Cells(x, "BA")
s2.Cells(sira, 54) = Cells(x, "BB")
s2.Cells(sira, 55) = Cells(x, "BD")
s2.Cells(sira, 56) = Cells(x, "BC")
s2.Cells(sira, 57) = Cells(x, "BD")
s2.Cells(sira, 58) = Cells(x, "BE")
s2.Cells(sira, 59) = Cells(x, "BF")
s2.Cells(sira, 60) = Cells(x, "BG")
s2.Cells(sira, 61) = Cells(x, "BH")
s2.Cells(sira, 62) = Cells(x, "BI")
s2.Cells(sira, 63) = Cells(x, "BJ")
s2.Cells(sira, 64) = Cells(x, "BK")
s2.Cells(sira, 65) = Cells(x, "BL")
s2.Cells(sira, 66) = Cells(x, "BM")
s2.Cells(sira, 67) = Cells(x, "BN")
s2.Cells(sira, 68) = Cells(x, "BO")
s2.Cells(sira, 69) = Cells(x, "BP")
s2.Cells(sira, 70) = Cells(x, "BQ")
s2.Cells(sira, 71) = Cells(x, "BR")
s2.Cells(sira, 72) = Cells(x, "BS")
s2.Cells(sira, 73) = Cells(x, "BT")
s2.Cells(sira, 74) = Cells(x, "BU")
s2.Cells(sira, 75) = Cells(x, "BV")
s2.Cells(sira, 76) = Cells(x, "BW")
s2.Cells(sira, 77) = Cells(x, "BY")
s2.Cells(sira, 78) = Cells(x, "BZ")
s2.Cells(sira, 79) = Cells(x, "CA")
s2.Cells(sira, 80) = Cells(x, "CB")
s2.Cells(sira, 81) = Cells(x, "CC")
s2.Cells(sira, 82) = Cells(x, "CD")
s2.Cells(sira, 83) = Cells(x, "CE")
s2.Cells(sira, 84) = Cells(x, "CF")
s2.Cells(sira, 85) = Cells(x, "CG")
s2.Cells(sira, 86) = Cells(x, "CH")
s2.Cells(sira, 87) = Cells(x, "CI")
s2.Cells(sira, 88) = Cells(x, "CJ")
s2.Cells(sira, 89) = Cells(x, "CK")
s2.Cells(sira, 90) = Cells(x, "CL")
s2.Cells(sira, 91) = Cells(x, "CM")
s2.Cells(sira, 92) = Cells(x, "CN")
s2.Cells(sira, 93) = Cells(x, "CO")
s2.Cells(sira, 94) = Cells(x, "CP")
s2.Cells(sira, 95) = Cells(x, "CQ")
s2.Cells(sira, 96) = Cells(x, "CR")
s2.Cells(sira, 97) = Cells(x, "CS")
s2.Cells(sira, 98) = Cells(x, "CT")
s2.Cells(sira, 99) = Cells(x, "CU")
s2.Cells(sira, 100) = Cells(x, "CV")
s2.Cells(sira, 101) = Cells(x, "CW")
s2.Cells(sira, 102) = Cells(x, "CX")
s2.Cells(sira, 103) = Cells(x, "CY")
s2.Cells(sira, 104) = Cells(x, "CZ")
s2.Cells(sira, 105) = Cells(x, "DA")
s2.Cells(sira, 106) = Cells(x, "DB")
s2.Cells(sira, 107) = Cells(x, "DC")
s2.Cells(sira, 108) = Cells(x, "DD")
s2.Cells(sira, 109) = Cells(x, "DE")
s2.Cells(sira, 110) = Cells(x, "DF")

Case Else:
For y = 1 To 41
s2.Cells(sira, y) = Cells(x, y + 1)
Next y
End Select
Next x
Sheets("ANASAYFA").Select
Range("A2:H80").ClearContents
Range("J2:J80").ClearContents
Range("M2F80").ClearContents
Range("B2:B80").Value = CDate(Format((Date + 1), "dd.mm.yyyy"))
10 MsgBox "CARİLERE AKTARILDI"
End Sub
Bo kodda STOK sayfasına aktarmanın daha kısası nasıl olur ?Yapmak istediğim
sayfalara aktarırken sadece STOK sayfasına A sütünundan itibaren,diğerlerine ise B sütunundan itibaren aktarsın istiyorum.

 

[Ayhan Ercan]

şöyle deneyiniz...

Sub sonerolaktarma299()
Sheets("ANASAYFA").Select
For x = 2 To [A65536].End(3).Row
Set s2 = Sheets(Cells(x, 1).Text)
sira = s2.[A65536].End(3).Row + 1
Select Case s2.Name
Case "STOK"
For i = 1 to 110
s2.Cells(sira, i) = Cells(x, i)
next i
Case Else:
For y = 1 To 41
s2.Cells(sira, y) = Cells(x, y + 1)
Next y
End Select
Next x
Sheets("ANASAYFA").Select
Range("A2:H80").ClearContents
Range("J2:J80").ClearContents
Range("M2:DF80").ClearContents
Range("B2:B80").Value = CDate(Format((Date + 1), "dd.mm.yyyy"))
10 MsgBox "CARİLERE AKTARILDI"
End Sub

 

[aydgur]

Aktarma kısaltması

dosyayı kısalltarak yolluyorum birde siz baksanız

 

[aydgur]

Pardon dosyayı gönderemedim