Thread: Copy/Paste Cells and put into new sheets

Hello all,

I need some help on a task I need to carry out. I am attaching a sample file.
What I want to do is to copy some columns in order to run some regressions. So, for example i need to copy the columns of aug-96 and sep-96 from the sheet "main" and put them in a new sheet called "sep-96", then copy column sep-96 and oct-96 and paste them in a new sheet called "oct-96", and so on... (I need the second column of each month, not the one with the date on it).

Thanks for your time,

Costas

hi, Costasg, please check attachment, run code "test", hope this helps

Hi watersev,

Thanks a lot for your help (once again). Now, in the new sheets I would like to delete the rows that contain "DIV". I found a macro, but it can be used only for the active sheet. How can i transform it to apply to all sheets (except the main)?

Many thanks again.

PHP Code: 

Sub DeleteRows()

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    Dim SearchCriteria As String:   SearchCriteria = "DIV"
    
    ActiveSheet.Range("A1").Activate
    On Error GoTo FoundAll
    Dim CriteriaRow As Long
    CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Row
    
    While CriteriaRow > 0
        ActiveSheet.Rows(CriteriaRow & ":" & CriteriaRow).Delete shift:=xlUp
        On Error GoTo FoundAll
        CriteriaRow = Cells.Find(What:=SearchCriteria, After:=ActiveCell, LookIn:=xlFormulas, _
                        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                        MatchCase:=False, SearchFormat:=False).Row
    Wend
    GoTo FoundAll
    
FoundAll:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Exit Sub
    
End Sub