Vba copy paste macro

B

batuka

New Member
Joined
Jul 14, 2017
Messages
32
Code: 
Sub sumcopy()

'April/2017












Dim first_day, lasy_day, month, year As Integer
Dim mmonth As String
Dim source_file As String








month = InputBox("Please enter the Month", "TCU//QNB Finansbank")
year = Worksheets("Macro").Cells(6, 2)




'Hangi ay hangi sayfada onu buluyoruz.


 If month = 1 Then
            mmonth = "January"
 ElseIf month = 2 Then
            mmonth = "February"
 ElseIf month = 3 Then
            mmonth = "March"
 ElseIf month = 4 Then
            mmonth = "April"
 ElseIf month = 5 Then
            mmonth = "May"
 ElseIf month = 6 Then
            mmonth = "June"
 ElseIf month = 7 Then
            mmonth = "July"
 ElseIf month = 8 Then
            mmonth = "August"
 ElseIf month = 9 Then
            mmonth = "September"
 ElseIf month = 10 Then
            mmonth = "October"
 ElseIf month = 11 Then
            mmonth = "November"
 ElseIf month = 12 Then
            mmonth = "December"
 End If










'target file
target_path = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylık Özet"
target_mainfile = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"


Dim Column, Column_2 As Integer


wb = ActiveWorkbook.Name




'target file


If aay = "January" Then


Workbooks(wb).Worksheets(1).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "February" Then


Workbooks(wb).Worksheets(2).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "March" Then


Workbooks(wb).Worksheets(3).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2




ElseIf aay = "April" Then


Workbooks(wb).Worksheets(4).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "May" Then


Workbooks(wb).Worksheets(5).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "June" Then


Workbooks(wb).Worksheets(6).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2




ElseIf aay = "July" Then


Workbooks(wb).Worksheets(7).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2




ElseIf aay = "August" Then


Workbooks(wb).Worksheets(8).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "September" Then


Workbooks(wb).Worksheets(9).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "October" Then


Workbooks(wb).Worksheets(10).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "November" Then


Workbooks(wb).Worksheets(11).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






ElseIf aay = "December" Then


Workbooks(wb).Worksheets(12).Activate
ActiveSheet.Range("a4").Select
Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2






End If




j = 2


Do Until ActiveSheet.Cells(3, j) = ""




first_day_date = Cells(3, j)




If Day(first_day_date) <= 9 Then


first_day = "0" & Day(first_day_date)




Else


first_day = Day(first_day_date)


End If


'source file


If month <= 9 Then


source_path = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
source_file = source_path & "\CORE-MİZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilk_gun & ".xlsx"
Else
source_path = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(" & ay & ")_" & aay
source_file = source_path & "\CORE-MİZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilk_gun & ".xlsx"
End If




'open file


Workbooks.Open (source_file), ReadOnly:=True
wb_core = ActiveWorkbook.Name


'to get daily summary of poz


ActiveWorkbook.Worksheets("summary").Activate
        
            i = 5
            Do Until i = 24
            
            
            If aay = "January" Then
                    
            Workbooks(wb).Worksheets(1).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
            
                       
            ElseIf aay = "February" Then
            
            Workbooks(wb).Worksheets(2).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
           
            ElseIf aay = "March" Then
            
            Workbooks(wb).Worksheets(3).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
             
            
            ElseIf aay = "April" Then
                       
            
            Workbooks(wb).Worksheets(4).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
             
            ElseIf aay = "May" Then
                       
            Workbooks(wb).Worksheets(5).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
             
            ElseIf aay = "June" Then
            Workbooks(wb).Worksheets(6).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
            
            ElseIf aay = "July" Then
                        
            Workbooks(wb).Worksheets(7).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
             
            ElseIf aay = "August" Then
            
            Workbooks(wb).Worksheets(8).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
           
            ElseIf aay = "September" Then
            
            Workbooks(wb).Worksheets(9).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
            ElseIf aay = "October" Then
            Workbooks(wb).Worksheets(10).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
            
            ElseIf aay = "November" Then
            
            Workbooks(wb).Worksheets(11).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
           
            ElseIf aay = "December" Then
            Workbooks(wb).Worksheets(Sheet12).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
          
            End If
            
            i = i + 1
                  
            Loop
            
Loop




Workbooks(wb).Save




















End Sub

hi here is my code which i want to do open daily files and copy the exact same place to my main file my main file got the dates on Column like 01/01/2017 it supposed to do when i write the month like 01 open all the 01 daily files and copy the place which i wrote in the code and get them to the main file when i write the month number like 07 it only gets the first day of the month help please thanks
 
Sort by date Sort by votes
Fluff said:
If I'm understanding this correctly, for a file created on the 26th July 2017 you'd have a file path & file name like
G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_2017\Bilanço_Kontrolleri\Core_Pozisyon\(07)_July\
CORE-MIZAN Pozisyon Kontrolü_2017-07-26.xlsx
And in row 3 starting in column B you have the dates the files were created.
is this correct?
Click to expand...

yea and the code counts the dates and when it finished and can't find any more days it stops and closes
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Untested, but try this on a copy
Code: 
Sub core_poz_ozet()
 
'Macro by Damla, April/2017
 
    Dim ilk_gun, son_gun, ay, yil As Integer
    Dim aay As String
    Dim kaynak_dosya As String
    Dim hedef_dizin
    Dim hedef_anadosya
    Dim sutun, sutun_2 As Integer
    Dim Wb As String
    Dim wb_core
    Dim J As Long, I As Long
    Dim ilk_gun_tarih As Long
    Dim kaynak_dizin

    'gun = Worksheets("Macro").Cells(4, 2)
    'ay = Worksheets("Macro").Cells(5, 2)
    'yil = Worksheets("Macro").Cells(6, 2)
    'aay = Worksheets("Macro").Cells(7, 2)
    'tarih = Worksheets("Macro").Cells(1, 5)
    't = InputBox("Lütfen tarih giriniz", "TCU // QNB Finansbank", tarih)
     
    ay = InputBox("Lütfen Dosyanin Yapilacagi Ayi Sayi ile yaziniz", "TCU//QNB Finansbank")
    yil = Worksheets("Macro").Cells(6, 2)
     
    'Hangi ay hangi sayfada onu buluyoruz.
     
    If ay = 1 Then
                  aay = "Ocak"
    ElseIf ay = 2 Then
                  aay = "Subat"
    ElseIf ay = 3 Then
                  aay = "Mart"
    ElseIf ay = 4 Then
                  aay = "Nisan"
    ElseIf ay = 5 Then
                  aay = "Mayis"
    ElseIf ay = 6 Then
                  aay = "Haziran"
    ElseIf ay = 7 Then
                  aay = "Temmuz"
    ElseIf ay = 8 Then
                  aay = "Agustos"
    ElseIf ay = 9 Then
                aay = "Eylül"
    ElseIf ay = 10 Then
                aay = "Ekim"
    ElseIf ay = 11 Then
                aay = "Kasim"
    ElseIf ay = 12 Then
                aay = "Aralik"
    End If
     
'    'hedef dosya
'    hedef_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylik Özet"
'    hedef_anadosya = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"
    
    Wb = ActiveWorkbook.Name
     
     
    'hedef dosya sütun saydirma
    Workbooks(Wb).Worksheets(ay).Activate
     
    J = 2
     
    Do Until ActiveSheet.Cells(3, J) = ""
        ilk_gun = WorksheetFunction.Text(Day(Cells(3, J)), "00")
        
        'kaynak dosya
        If ay <= 9 Then
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilk_gun & ".xlsx"
        Else
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilk_gun & ".xlsx"
        End If
         
        'pozisyon dosyasi açma
         
        Workbooks.Open Filename:=kaynak_dosya, ReadOnly:=True
        wb_core = ActiveWorkbook.Name
         
        'Özet total pozisyonu günlük bazda getirmek
         
        ActiveWorkbook.Worksheets("summary").Activate
               
        I = 5
        Do Until I = 24
            Workbooks(Wb).Worksheets(ay).Cells(I - 1, J).Value = Workbooks(wb_core).Worksheets("summary").Cells(I, 7).Value
        
            I = I + 1
        
        Loop
        J = J + 1
    Loop

    Workbooks(Wb).Save
 End Sub
 
Upvote 0
Fluff said:
Untested, but try this on a copy
Code: 
Sub core_poz_ozet()
 
'Macro by Damla, April/2017
 
    Dim ilk_gun, son_gun, ay, yil As Integer
    Dim aay As String
    Dim kaynak_dosya As String
    Dim hedef_dizin
    Dim hedef_anadosya
    Dim sutun, sutun_2 As Integer
    Dim Wb As String
    Dim wb_core
    Dim J As Long, I As Long
    Dim ilk_gun_tarih As Long
    Dim kaynak_dizin

    'gun = Worksheets("Macro").Cells(4, 2)
    'ay = Worksheets("Macro").Cells(5, 2)
    'yil = Worksheets("Macro").Cells(6, 2)
    'aay = Worksheets("Macro").Cells(7, 2)
    'tarih = Worksheets("Macro").Cells(1, 5)
    't = InputBox("Lütfen tarih giriniz", "TCU // QNB Finansbank", tarih)
     
    ay = InputBox("Lütfen Dosyanin Yapilacagi Ayi Sayi ile yaziniz", "TCU//QNB Finansbank")
    yil = Worksheets("Macro").Cells(6, 2)
     
    'Hangi ay hangi sayfada onu buluyoruz.
     
    If ay = 1 Then
                  aay = "Ocak"
    ElseIf ay = 2 Then
                  aay = "Subat"
    ElseIf ay = 3 Then
                  aay = "Mart"
    ElseIf ay = 4 Then
                  aay = "Nisan"
    ElseIf ay = 5 Then
                  aay = "Mayis"
    ElseIf ay = 6 Then
                  aay = "Haziran"
    ElseIf ay = 7 Then
                  aay = "Temmuz"
    ElseIf ay = 8 Then
                  aay = "Agustos"
    ElseIf ay = 9 Then
                aay = "Eylül"
    ElseIf ay = 10 Then
                aay = "Ekim"
    ElseIf ay = 11 Then
                aay = "Kasim"
    ElseIf ay = 12 Then
                aay = "Aralik"
    End If
     
'    'hedef dosya
'    hedef_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylik Özet"
'    hedef_anadosya = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"
    
    Wb = ActiveWorkbook.Name
     
     
    'hedef dosya sütun saydirma
    Workbooks(Wb).Worksheets(ay).Activate
     
    J = 2
     
    Do Until ActiveSheet.Cells(3, J) = ""
        ilk_gun = WorksheetFunction.Text(Day(Cells(3, J)), "00")
        
        'kaynak dosya
        If ay <= 9 Then
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilk_gun & ".xlsx"
        Else
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilk_gun & ".xlsx"
        End If
         
        'pozisyon dosyasi açma
         
        Workbooks.Open Filename:=kaynak_dosya, ReadOnly:=True
        wb_core = ActiveWorkbook.Name
         
        'Özet total pozisyonu günlük bazda getirmek
         
        ActiveWorkbook.Worksheets("summary").Activate
               
        I = 5
        Do Until I = 24
            Workbooks(Wb).Worksheets(ay).Cells(I - 1, J).Value = Workbooks(wb_core).Worksheets("summary").Cells(I, 7).Value
        
            I = I + 1
        
        Loop
        J = J + 1
    Loop

    Workbooks(Wb).Save
 End Sub
Click to expand...

got debug on Workbooks(Wb).Worksheets(ay).Activate
 
Upvote 0
Some of the variables had not been declared properly.
Try this
Code: 
Sub core_poz_ozet()
 
'Macro by Damla, April/2017
    Dim ilk_gun As String, ay As Long, yil As Long
    Dim aay As String
    Dim kaynak_dosya As String
'    Dim hedef_dizin
'    Dim hedef_anadosya
'    Dim sutun, sutun_2 As Integer
    Dim Wb As String
    Dim wb_core As String
    Dim j As Long, I As Long
    Dim ilk_gun_tarih As Long
    Dim kaynak_dizin As String

    'gun = Worksheets("Macro").Cells(4, 2)
    'ay = Worksheets("Macro").Cells(5, 2)
    'yil = Worksheets("Macro").Cells(6, 2)
    'aay = Worksheets("Macro").Cells(7, 2)
    'tarih = Worksheets("Macro").Cells(1, 5)
    't = InputBox("Lütfen tarih giriniz", "TCU // QNB Finansbank", tarih)
     
    ay = InputBox("Lütfen Dosyanin Yapilacagi Ayi Sayi ile yaziniz", "TCU//QNB Finansbank")
'    yil = Worksheets("Macro").Cells(6, 2)
     
    'Hangi ay hangi sayfada onu buluyoruz.
     
    If ay = 1 Then
                  aay = "Ocak"
    ElseIf ay = 2 Then
                  aay = "Subat"
    ElseIf ay = 3 Then
                  aay = "Mart"
    ElseIf ay = 4 Then
                  aay = "Nisan"
    ElseIf ay = 5 Then
                  aay = "Mayis"
    ElseIf ay = 6 Then
                  aay = "Haziran"
    ElseIf ay = 7 Then
                  aay = "Temmuz"
    ElseIf ay = 8 Then
                  aay = "Agustos"
    ElseIf ay = 9 Then
                aay = "Eylül"
    ElseIf ay = 10 Then
                aay = "Ekim"
    ElseIf ay = 11 Then
                aay = "Kasim"
    ElseIf ay = 12 Then
                aay = "Aralik"
    End If
     
'    'hedef dosya
'    hedef_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylik Özet"
'    hedef_anadosya = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"
    
    Wb = ActiveWorkbook.Name
     
     
    'hedef dosya sütun saydirma
    Workbooks(Wb).Worksheets(ay).Activate
     
    j = 2
     
    Do Until ActiveSheet.Cells(3, j) = ""
        ilk_gun = WorksheetFunction.Text(Day(Cells(3, j)), "00")
        'kaynak dosya
        If ay <= 9 Then
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilk_gun & ".xlsx"
        Else
            kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(" & ay & ")_" & aay
            kaynak_dosya = kaynak_dizin & "\CORE-MIZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilk_gun & ".xlsx"
        End If
         
        'pozisyon dosyasi açma
         
        Workbooks.Open (kaynak_dosya), ReadOnly:=True
        wb_core = ActiveWorkbook.Name
         
        'Özet total pozisyonu günlük bazda getirmek
         
        ActiveWorkbook.Worksheets("summary").Activate
               
        I = 5
        Do Until I = 24
            Workbooks(Wb).Worksheets(ay).Cells(I - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(I, 7).Value
        
            
            I = I + 1
        
        Loop
        j = j + 1
    Loop

    Workbooks(Wb).Save
 End Sub
For your information a Dim staement like this
Dim ilk_gun, son_gun, ay, yil As Integer
only yil is declared as integer, the other 3 are all variant
 
Upvote 0
Fluff said:
If I'm understanding this correctly, for a file created on the 26th July 2017 you'd have a file path & file name like
G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_2017\Bilanço_Kontrolleri\Core_Pozisyon\(07)_July\
CORE-MIZAN Pozisyon Kontrolü_2017-07-26.xlsx
And in row 3 starting in column B you have the dates the files were created.
is this correct?
Click to expand...

yes i ve a file in row 3 starting in column B you have the dates the files were created.also in that file i ve 12 sheets for every month For example Ocak(January),subat(february) tc 12 sheets for the 12 months and all has the working dates which stars at row 3 starting column B then i ve a different folder with excel files in it daily files like file name is Ocak(January) and it has 20 excel files becouse January has 20 workdays
my main file name is core pozisyon ozet i need to open 1st of january file copy the area in the code and pase it to the my mainfile which is core pozisyon then close it.then i need to open 2nd of january and do the same thing again but this time i need to start from row 3 column C.
 
Upvote 0
Fluff said:
Some of the variables had not been declared properly.
Try thisFor your information a Dim staement like this
Dim ilk_gun, son_gun, ay, yil As Integer
only yil is declared as integer, the other 3 are all variant
Click to expand...

hey i ve upgraded my macro and i got only 1 problem
Code: 
Private Sub CommandButton1_Click()Dim ilk_gun, son_gun, ay, yil As Integer
Dim ilkGun As String
Dim aay As String
Dim kaynak_dosya As String


Application.Calculation = XlCalculation.xlCalculationManual
     Application.DisplayAlerts = False
ay = InputBox("Lütfen Dosyanın Yapılacağı Ayı Sayı ile yazınız", "TCU//QNB Finansbank")
Application.ScreenUpdating = False


If ay = 1 Then
aay = "Ocak"


ElseIf ay = 2 Then
aay = "Şubat"


ElseIf ay = 3 Then
aay = "Mart"


ElseIf ay = 4 Then
aay = "Nisan"


ElseIf ay = 5 Then
aay = "Mayıs"


ElseIf ay = 6 Then
aay = "Haziran"


ElseIf ay = 7 Then
aay = "Temmuz"


ElseIf ay = 8 Then
aay = "Ağustos"


ElseIf ay = 9 Then
aay = "Eylül"


ElseIf ay = 10 Then
aay = "Ekim"


ElseIf ay = 11 Then
aay = "Kasım"


ElseIf ay = 12 Then
aay = "Aralık"


End If


'hedef_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylık Özet"
'hedef_anadosya = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"
'hedef_dizin = "E:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\Core Pozisyon Aylık Özet"
'hedef_anadosya = "E:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\" & "Core Pozisyon Özet" & ".xlsm"


wb = ActiveWorkbook.Name


If aay = "Ocak" Then
Workbooks(wb).Worksheets(1).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Şubat" Then
Workbooks(wb).Worksheets(2).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Mart" Then
Workbooks(wb).Worksheets(3).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Nisan" Then
Workbooks(wb).Worksheets(4).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Mayıs" Then
Workbooks(wb).Worksheets(5).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Haziran" Then
Workbooks(wb).Worksheets(6).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Temmuz" Then
Workbooks(wb).Worksheets(7).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Ağustos" Then
Workbooks(wb).Worksheets(8).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Eylül" Then
Workbooks(wb).Worksheets(9).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Ekim" Then
Workbooks(wb).Worksheets(10).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Kasım" Then
Workbooks(wb).Worksheets(11).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


ElseIf aay = "Aralık" Then
Workbooks(wb).Worksheets(12).Activate
ActiveSheet.Range("b3").Select
ActiveSheet.Range(Selection, Selection.End(xlToRight)).Select
sutun = Application.Count(Selection) + 2


End If
j = 2
Do Until ActiveSheet.Cells(3, j) = " "


    ilk_gun_tarih = Worksheets(1).Cells(3, j)
    
        If Day(ilk_gun_tarih) <= 9 Then
        ilkGun = "0" & Day(ilk_gun_tarih)
        yil = Year(ilk_gun_tarih)
        
        Else
        ilkGun = Day(ilk_gun_tarih)
        yil = Year(ilk_gun_tarih)
        
        End If
        
        If ay <= 9 Then
        kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
        kaynak_dosya = kaynak_dizin & "\CORE-MİZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilkGun & ".xlsx"
        'kaynak_dizin = "E:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(0" & ay & ")_" & aay
        'kaynak_dosya = kaynak_dizin & "\CORE-MİZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilkGun & ".xlsx"
        dosya_kapama = "CORE-MİZAN Pozisyon Kontrolü_" & yil & "-0" & ay & "-" & ilkGun & ".xlsx"
        
        Else
        kaynak_dizin = "G:\HOME\MALIKONT\_Hazine_Kontrol\TCU_" & yil & "\Bilanço_Kontrolleri\Core_Pozisyon\(" & ay & ")_" & aay
        kaynak_dosya = kaynak_dizin & "\CORE-MİZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilkGun & ".xlsx"
        dosya_kapama = "CORE-MİZAN Pozisyon Kontrolü_" & yil & "-" & ay & "-" & ilkGun & ".xlsx"
        
        End If
        
    Workbooks.Open kaynak_dosya, Editable
    wb_core = ActiveWorkbook.Name
    ActiveWorkbook.Worksheets("summary").Activate
    i = 5
        Do Until i = 24
        If aay = "Ocak" Then
        Workbooks(wb).Worksheets(1).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Şubat" Then
        Workbooks(wb).Worksheets(2).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Mart" Then
        Workbooks(wb).Worksheets(3).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Nisan" Then
        Workbooks(wb).Worksheets(4).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Mayıs" Then
        Workbooks(wb).Worksheets(5).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Haziran" Then
        Workbooks(wb).Worksheets(6).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Temmuz" Then
        Workbooks(wb).Worksheets(7).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Ağustos" Then
        Workbooks(wb).Worksheets(8).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Eylül" Then
        Workbooks(wb).Worksheets(9).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Ekim" Then
        Workbooks(wb).Worksheets(10).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Kasım" Then
        Workbooks(wb).Worksheets(11).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        ElseIf aay = "Aralık" Then
        Workbooks(wb).Worksheets(Sheet12).Cells(i - 1, j).Value = Workbooks(wb_core).Worksheets("summary").Cells(i, 7).Value
        End If
        i = i + 1
        Loop
        Workbooks(dosya_kapama).Close SaveChanges:=False
        j = j + 1
    Loop
Workbooks(wb).Save
Application.ScreenUpdating = True


End Sub
my last problem is the part
Code: 
 ilk_gun_tarih = Worksheets(1).Cells(3, j)
my problem is Worksheets 1 mean Ocak and if i wanted to do for other months it gives error becouse workdays on months are different how can make it work other things are fixed it works perfectly but one thing it can't see the firs_gun_tarih which means first_day_date i need to find every months first day and all are on b3 on different sheets we got 12 sheets i can find it withc hanging Worksheets(2) or 3 or 4 manually but i couldn't figure out how to work for all the months
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Filtering values from data source and pasting it into a second workbook
2
Replies
17
Views
900
kevin9999
K
Nuke_It_Newport
  • Question
VBA Copy / Paste of row
Replies
1
Views
526
Nuke_It_Newport
Nuke_It_Newport
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
331
RoryA
RoryA
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
406
ummjay
U
A
  • Question
VBA macro to format excel worksheet.
Replies
1
Views
1K
HaHoBe
HaHoBe

Forum statistics

Threads
1,214,921
Messages
6,122,280
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top