Copying an entire sheet into another workbook

[chamster]

I'd like to finish off my macro by copying a certain worksheet into a collecting workbook. What is the recommended way to do that?

As in:
a) get all the contents of worksheet called "yaba" in the current workbook
b) create a page called "daba" in a workbook called "doo"
c) cause the page "daba" in "doo" to be a copy of "yaba"

I have no idea how to do that syntaxwise so everything from suggested method names, through pieces of code up to pointed out gotha's will be greatly received and thankfully regarded.

[herzberg]

This is what I would do:

[vba]Public Sub AddNew()

Dim SheetCount As Long
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet
Dim Targetwb As Workbook
Dim SourceRange As Range

'This sets up the range to be copied
Set SourceSheet = ThisWorkbook.Sheets("yaba")
Set SourceRange = SourceSheet.Range("A1:IV65536")

'This opens the target workbook
Workbooks.Open ("doo")
Set Targetwb = Workbooks("doo")

With Targetwb
'This returns the number of worksheets
SheetCount = Targetwb.Sheets.Count

'This adds the new sheet at the end
.Sheets.Add After:=ThisWorkbook.Sheets(SheetCount)

'This returns the new number of worksheets
SheetCount = .Sheets.Count

'This sets up the last sheet, i.e. the newly added sheet
Set TargetSheet = .Sheets(SheetCount)

'This renames, then copies and pastes the data
With TargetSheet
.Name = "daba"
SourceRange.Copy .Range("A1")
End With
.Save
.Close
End With

Set Targetwb = Nothing
Set TargetSheet = Nothing
Set SourceSheet = Nothing
Set SourceRange = Nothing

End Sub[/vba]
I'm didn't test it out but I *think* the code goes something like this. Oh yes, I'm assuming the workbook for which the data is to be pasted into, i.e. "doo", is not opened initially.

[rory]

If you don't have links to worry about, you can just copy the sheet:
[VBA]activeworkbook.sheets("yaba").copy after:=workbooks("doo").sheets(1)
workbooks("doo").sheets("yaba").Name = "daba"
[/VBA]

[chamster]

Nice! Thans to both of you guys.

[chamster]

If you don't have links to worry about, you can just copy the sheet:
[vba]activeworkbook.sheets("yaba").copy after:=workbooks("doo").sheets(1)
workbooks("doo").sheets("yaba").Name = "daba"
[/vba]

Sorry to tell you that but it didn't work out. For some reason, i've get the wanted behavior few times but generally, it doesn't work. I get errors due to something i can't explain (error number 1004). This is the exact piece of code i'm running.
[VBA]
Private Sub ExportData()
On Error GoTo errHandler
ActiveWorkbook.Sheets("Summary").Copy After:=Workbooks("Summaries.xls").Sheets(1)

Exit Sub
errHandler:
Application.DisplayAlerts = True
Dim mess As String
mess = "error occured in ExportData" & Chr(13) & "Error # " & Str(Err.Number) & _
" was generated by " & Err.Source & Chr(13) & Err.Description
MsgBox mess, , "Error", Err.HelpFile, Err.HelpContext
End Sub
[/VBA]

When i execute
[VBA]
debug.Print ActiveWorkbook.Name & " and " & Workbooks("Summaries.xls").Sheets(1).name
[/VBA]
i get the actual names and they are correct as far i can tell.

What's up here?!

[rory]

Do you get an error with this, and if so, on which line:
[VBA]Private Sub ExportData()
Dim wbkSource As Workbook, wbkDest As Workbook
Set wbkSource = ActiveWorkbook
Set wbkDest = Workbooks("Summaries.xls")
wbkSource.Sheets("Summary").Copy After:=wbkDest.Sheets(1)

End Sub
[/VBA]

[chamster]

I might have found the error. When we put in a new sheet in a workbook, it gets activated, right? Then ActiveSheet points to something else... Is there a good way not to change the focus all the time? In fact, i'd like to put in the new sheets and data into a workbook that is closed. Doable?

[rory]

Activesheet is not in your code??
No, you can't do that to a closed workbook.

[chamster]

ActiveSheet WAS in my code. Then, as i added a sheet in the other workbook, the active sheet changed (since the active workbook changed). I think i'm just not used to the computer "helping" me. It's quite nice, actually. Nevertheless, it's friday, the work is over for the week so i suggest we let eachother be for a few days.

Have a nice weekend Rory.

[rory]

Likewise!
(it wasn't in any of the code you posted though)

[Norie]

chamster

I have to agree with rory, ActiveSheet isn't in any of the code you posted.

[chamster]

Ooops, i was confusing ActiveSheet and ActiveWorkbook. It's the latter i used. Do i have to use ActiveSheet? I'd prefer to copy from/to sheets without activating them. It gives me a feeling of more independency.

[Norie]

You shouldn't need to activate/select anything.

[daniel_d_n_r]

You can use this kind of thing too...

[vba]Sheets("Sheet3").Move Before:=Workbooks("myworkbook.xls").Sheets(1)
[/vba]

pretty sure you have to have the workbook open (well thats how it works manually)that your moving the sheet to, but it could easily be coded in

[chamster]

Grrr! I don't get it at all. I'm running the code below and i get errors (the informative type, 1004) when i execute the copy-command.
[vba]
Private Sub ExportData()
On Error GoTo errHandler
Dim wb As Workbook, endOfSummaries As Integer, nameToBe As String
Set wb = ActiveWorkbook
endOfSummaries = Workbooks("Summaries.xls").Worksheets.Count
nameToBe = "test"
On Error Resume Next
Workbooks("Summaries.xls").Sheets(nameToBe).Delete
On Error GoTo errHandler
ActiveWorkbook.Worksheets("Summary").Copy After:=Workbooks("Summaries.xls").Worksheets(1)
End Sub
[/vba]

I have managed to copy the sheets but for some reason, it doesn't work now. What kind of retarded thing have i done now?

[chamster]

Do you get an error with this, and if so, on which line:
[vba]Private Sub ExportData()
Dim wbkSource As Workbook, wbkDest As Workbook
Set wbkSource = ActiveWorkbook
Set wbkDest = Workbooks("Summaries.xls")
wbkSource.Sheets("Summary").Copy After:=wbkDest.Sheets(1)
End Sub
[/vba]

Sorry, i must have missed this post or something. Yes, i get an error and it's on the copy-line. Both workbooks are open, no, joined cells, no protections. And the strangest part is that i actually did copy over some things. Then, it stopped working without any apparent change (apparent to me, as i didn't change the semantics of the code, that is).

Also, i need to add this. I have restarted Excel and the code you gave me worked. I have a very strong feeling that after a while, it will cease to do so. Is it a bug or am i going mad?

[Bob Phillips]

How about workbooks to see what is up

[rory]

Either you are going mad or something is not what you think it is. Try breaking it down further and see what errors:
[VBA]Private Sub ExportData()
Dim wbkSource As Workbook, wbkDest As Workbook
Dim objSource As Object, objDest As Object
Set wbkSource = ActiveWorkbook
Set objSource = wbkSource.Sheets("Summary")
Set objDest = wbkDest.Sheets(1)
Set wbkDest = Workbooks("Summaries.xls")
objSource.Copy After:=objDest
End Sub
[/VBA]

[chamster]

I get errors on Set objDest...

Why this order? I'd expect the wbkDest to be set first. What do you think?

When i change the order of the lines i get it to work nicely. On the other hand, it seems like restarting Excel solves the problem for a while. Comments?

After i've run the program a couple of times, the code you gave me stops working. The copy-part, i.e. the last line, is causing the error. But the first few times, it flows nicely. I'm pretty certain that it's not the code that's to blame. It's something else and i'll be damned if i know what it could be.

[rory]

Yes, sorry - I was in a hurry and added that line in the wrong order!
How many times are you doing the copy?