PDA

View Full Version : copying worksheets to workbooks



CatDaddy
10-06-2011, 12:26 PM
im getting a 1004 error "1004 excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook" when i tried to make vba cycle through open workbooks and append a the worksheet from my macro book that shares the same name:

Sub addColumns()
Dim i As Integer
Dim wb As Workbook
Dim wbName(1 To 5) As String
Dim cV As Integer
i = 1
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wbName(i) = Left(wb.Name, InStrRev(wb.Name, ".") - 1)
i = i + 1
End If
Next wb

For i = 1 To 5
Workbooks(wbName(i) & ".xlsx").Activate
Application.CutCopyMode = False
ThisWorkbook.Sheets(wbName(i)).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Next i

End Sub

ex: there is a workbook named NEWYORK.xlsx and i need the sheet in ThisWorkbook named "NEWYORK" to make a copy after all of the sheets in NEWYORK.xlsx

CatDaddy
10-06-2011, 12:50 PM
all of the new workbooks have only one sheet, so i changed
ThisWorkbook.Sheets(wbName(i)).Copy After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
to
ThisWorkbook.Sheets(wbName(i)).Copy After:=ActiveWorkbook.Sheets(1)

no success still

CatDaddy
10-06-2011, 01:02 PM
i resolved my problem by cheating...i still wonder why it wouldnt let me copy the worksheets themselves though....

i = 1
For Each wb In Application.Workbooks
If wb.Name <> ThisWorkbook.Name Then
wbName(i) = Left(wb.Name, InStrRev(wb.Name, ".") - 1)
i = i + 1
End If
Next wb

For i = 1 To 5
Workbooks(wbName(i) & ".xlsx").Activate
Range("A1").Activate

ActiveWorkbook.Sheets.Add after:=Sheets(1)
ActiveSheet.Name = "MANAGERS"

ThisWorkbook.Sheets(wbName(i)).Range("A1:I" & ThisWorkbook.Sheets(wbName(i)).Range("A5000").End(xlUp).Row).Copy _
Destination:=ActiveWorkbook.Sheets(2).Range("A1")

Next i