Taking columns from sheets

[Serdarrk]

Hello,

The following single file contains approximately 250 pages named by date. I need that opening new page in the file and take all pages' Y and Z columns starting from new page's D columns (I have to enter the names of the first three columns). I don't know it is possible but an other need when code copy and paste all Y and Z columns to new page, it must copy and paste name of every page to top of Y and Z columns (to 3.row). I hope I could tell. I will be very happy if you can help with this.


http://s7.dosya.tc/server3/9r93ou/01.02.2000-1.xls.html

 

[mumps]

Your file is extremely large. Could you post a file that has only a few sample sheets? Also include a sheet that shows the how you want the data to look like after the macro is run.

 

[Serdarrk]

Thank you for your interest. I added it down as you want.

http://s3.dosya.tc/server14/i42j5s/01.02.2000-1.xls.html

 

[mumps]

My antivirus software blocks the site when I try to download your file. Could you use dropbox.com or box.com?

 

[Serdarrk]

https://www.dropbox.com/s/ywyfbh4hie1zjlx/01.02.2000-1.xls?dl=0

 

[mumps]

I noticed that columns Y and Z in the 3 source sheets do not contain data. Does that mean that you don't want to copy the data in the columns but you want to move the data? Also, could you explain in detail what you mean by

I have to enter the names of the first three columns

Please refer to specific cells, columns, rows and sheets.

 

[Serdarrk]

No, sorry. It is my mistake. I want to copy Y and Z columns.But also it can move because if I can succes to make it I will use only new page. I mean, I wil write to A,B and C columns names of share certificate's . Because of that I need to start from D column. And also I need to sort pages by date. As you can see in the file, pages names like 01.02.2000, 01.03.2000,01.04.2000. I mean pages sorted by first number. They would be like 01.02.2000 , 02.02.2000,03.02.200....01.12.2000,02.12.2000 etc. But I couldn't succes it also.

 

[Serdarrk]

Also, I don't want to interfere your business but if it is more easy for you, you can make like all Y columns to new page1, and all Z columns to new page2 from D column and with their names to 3rd row.

 

[mumps]

Start by entering the names in A1, B1 and C1 in Sheet("Sayfa1"). Then try this macro.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim lColumn As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        lColumn = Sheets("Sayfa1").Cells(1, Columns.Count).End(xlToLeft).Column
        If ws.Name <> "Sayfa1" Then
            Sheets("Sayfa1").Cells(1, lColumn + 1).Resize(, 2) = ws.Name
            LastRow = ws.Range("Y" & ws.Rows.Count).End(xlUp).Row
            ws.Range("Y4:Z" & LastRow).Copy
            Sheets("Sayfa1").Cells(3, lColumn + 1).PasteSpecial xlPasteValues
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

 

[Serdarrk]

You are absolutely gorgeous.Respect your knowledge. Thank you for your interest,time and effort.