Copy Multiple Worksheets

[violette]

I need to copy multiple sheets into the template accordingly. well, i manage to copy for the first sheet only. i've tried to modify the code to copy multiple sheets at one time and i couldnt get it right. here's the code for copying the first sheet into the template. i have to specify the column as i just need certain column to be copied for that particular sheet.

i have five sheets; Year, Q1, Q2, Q3, Q4

here is my code;

Sub CopyCell()

   Dim wbk As Workbook
   Dim strFirstFile As String
   Dim strSecondFile As String
   
   strFirstFile = "C:\Documents and Settings\user\My Documents\FiST Mac\Data.xls"
   strSecondFile = "C:\Documents and Settings\user\My Documents\FiST Mac\FiST_data_template.xls"

   Set wbk = Workbooks.Open(strFirstFile)
   With wbk.Sheets("Year")
   Columns("A:AB").copy
   
   End With
   
   Set wbk = Workbooks.Open(strSecondFile)
   With wbk.Sheets("Year")
   Columns("B:AC").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
   End With
   wbk.SaveAs "C:\Documents and Settings\user\My Documents\FiST Mac\FISTdb.xls"
   wbk.Close

End Sub

[violette]

oh sorry i've copied the wrong code. here's the one that is working fine.;

Sub CopyCell()
 
    Dim wbk As Workbook
    Dim strFirstFile As String
    Dim strSecondFile As String
 
    strFirstFile = "C:\Documents and Settings\user\My Documents\FiST Mac\Data.xls"
    strSecondFile = "C:\Documents and Settings\user\My Documents\FiST Mac\FiST_data_template.xls"
 
    Set wbk = Workbooks.Open(strFirstFile)
    With wbk.Sheets("Year")
        .Range(.Range("A5:AJ5"), .Range("A65536").End(xlUp)).copy
 
    End With
 
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Yearly")
 
    Sheets("Yearly").[B65536:AK65536].End(xlUp)(2).PasteSpecial Paste:=xlValues
 
    End With
    wbk.SaveAs "C:\Documents and Settings\user\My Documents\FiST Mac\FISTdb.xls"
    wbk.Close
    Windows("Data.xls").Close
 
End Sub

[violette]

hi guys, i've found the solution ady :) here's the code for future reference. might be useful for others.

Sub Copy()
    Dim wbkFirst        As Workbook
    Dim wbkSecond       As Workbook
    Dim wksSheet        As Worksheet
    Dim strFirstFile    As String
    Dim strSecondFile   As String
     
     
    strFirstFile = "C:\Documents and Settings\user\My Documents\FiST Mac\Bloomberg.xls"
    strSecondFile = "C:\Documents and Settings\user\My Documents\FiST Mac\FiST_data_template.xls"
     
     
    Set wbkFirst = Workbooks.Open(strFirstFile)
    Set wbkSecond = Workbooks.Open(strSecondFile)
     
    For Each wksSheet In wbkFirst.Worksheets
        If wksSheet.Name = "Year" Then
            With wksSheet
                .Range("A5:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            End With
             
            With wbkSecond.Worksheets("Yearly")
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End With
             
        ElseIf wksSheet.Name = "Q1" Then
            With wksSheet
                .Range("A5:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            End With
            
            With wbkSecond.Worksheets("Q1")
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End With
            
        ElseIf wksSheet.Name = "Q2" Then
            With wksSheet
                .Range("A5:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            End With
            
            With wbkSecond.Worksheets("Q2")
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End With
            
        ElseIf wksSheet.Name = "Q3" Then
            With wksSheet
                .Range("A5:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            End With
            
            With wbkSecond.Worksheets("Q3")
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End With
            
        ElseIf wksSheet.Name = "Q4" Then
            With wksSheet
                .Range("A5:AJ" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
            End With
            
            With wbkSecond.Worksheets("Q4")
                .Range("B" & .Range("B" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues
            End With
            
            Else
            MsgBox "Error!", vbOKOnly, " FiST"
            
        End If
        
        
    Next wksSheet
     
    Application.DisplayAlerts = False
    wbkSecond.SaveAs "C:\Documents and Settings\user\My Documents\FiST Mac\FISTdb.xls"
    wbkFirst.Close
    MsgBox "FiST Database Updated", vbOKOnly, " FiST"
     
     
    Application.DisplayAlerts = True
     
End Sub