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]

You are very welcome.

 

[Serdarrk]

Can I ask one more question ?

 

[Serdarrk]

I need to copy all pages' A,B,C,Y and Z columns and paste to new sheets like Sayfa1,Sayfa2,Sayfa3,Sayfa4,Sayfa5 because Sayfa1 not enough for them. How must I change your code to make it ? And How can I limit rows until for example 280?

 

[mumps]

Just for clarification ... You now want to copy columns A, B, C, Y and Z starting in row 4 to a new sheet. Is this correct? Where in the new sheet do you want to paste the copied data and where do you want the sheet name to appear? When you say

How can I limit rows until for example 280?

do you mean that you don't want to copy the entire sheet but only 280 rows starting in row 4 of each sheet?

 

[Serdarrk]

Yes You understood me with this English thank you. I mean excatly copy for example until 280. rows from 4th rows. It must start from A colums of new sheet and also names must be in first rows and I should say to you, one sheet will not be enough for all then code should continue on all new sheets like Sayfa1, Sayfa2, Sayfa3, Sayfa4 and Sayfa5 ( Sayfa means sheet)

 

[mumps]

If you are using Excel 2007 or newer version, it will handle up to 16384 columns. In your original post, you mentioned that you had approximately 250 sheets. If you copy 5 columns from each sheet, that will give you a total of 1250 columns which leaves you with more than 16200 free columns on "Sayfa1". Older versions of Excel, before 2007, can handle only 256 columns. Which version of Excel are you using?

 

[Serdarrk]

I am using 2007 but if it is necassary I can try to set up 2010 or an other. İs 2010 enough to do it?

 

[mumps]

Excel 2007 can handle up to 16384 columns so it should be enough. Do you want me to suggest a macro so that it now copies columns A, B, C, Y and Z from each sheet?

 

[Serdarrk]

Yes, copying all sheets' A,B,C,Y and Z colums with their names to Sayfa1 side by side. I tired you a lot but I really need it.

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim lColumn As Long
    Dim ws As Worksheet
    For Each ws In Sheets
        lColumn = Sheets("Sayfa1").Cells(1, Columns.Count).End(xlToLeft).Column
        If lColumn = 1 Then lColumn = 0
        If ws.Name <> "Sayfa1" Then
            Sheets("Sayfa1").Cells(1, lColumn + 1).Resize(, 5) = ws.Name
            ws.Range("A4:C284").Copy
            Sheets("Sayfa1").Cells(3, lColumn + 1).PasteSpecial xlPasteValues
            ws.Range("Y4:Z284").Copy
            Sheets("Sayfa1").Cells(3, lColumn + 4).PasteSpecial xlPasteValues
        End If
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

This macro will copy only 280 rows from each sheet. Start with a blank sheet "Sayfa1".