PDA

View Full Version : Copying an entire sheet into another workbook



chamster
09-27-2007, 11:37 PM
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? :help

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. :bow:

herzberg
09-28-2007, 01:27 AM
This is what I would do:

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
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
09-28-2007, 02:10 AM
If you don't have links to worry about, you can just copy the sheet:
activeworkbook.sheets("yaba").copy after:=workbooks("doo").sheets(1)
workbooks("doo").sheets("yaba").Name = "daba"

chamster
09-28-2007, 04:01 AM
Nice! Thans to both of you guys.

chamster
09-28-2007, 06:04 AM
If you don't have links to worry about, you can just copy the sheet:
activeworkbook.sheets("yaba").copy after:=workbooks("doo").sheets(1)
workbooks("doo").sheets("yaba").Name = "daba"


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.

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


When i execute

debug.Print ActiveWorkbook.Name & " and " & Workbooks("Summaries.xls").Sheets(1).name

i get the actual names and they are correct as far i can tell.

What's up here?!

rory
09-28-2007, 06:15 AM
Do you get an error with this, and if so, on which line:
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

chamster
09-28-2007, 06:23 AM
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
09-28-2007, 06:37 AM
Activesheet is not in your code??
No, you can't do that to a closed workbook.

chamster
09-28-2007, 07:38 AM
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
09-28-2007, 07:51 AM
Likewise! :)
(it wasn't in any of the code you posted though)

Norie
09-28-2007, 09:25 AM
chamster

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

chamster
09-28-2007, 02:35 PM
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
09-28-2007, 04:30 PM
You shouldn't need to activate/select anything.

daniel_d_n_r
09-28-2007, 05:45 PM
You can use this kind of thing too...

Sheets("Sheet3").Move Before:=Workbooks("myworkbook.xls").Sheets(1)


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
10-01-2007, 12:30 AM
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.:banghead:

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


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
10-01-2007, 12:36 AM
Do you get an error with this, and if so, on which line:
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


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
10-01-2007, 01:51 AM
How about workbooks to see what is up

rory
10-01-2007, 01:57 AM
Either you are going mad or something is not what you think it is. Try breaking it down further and see what errors:
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

chamster
10-01-2007, 03:34 AM
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
10-01-2007, 05:17 AM
Yes, sorry - I was in a hurry and added that line in the wrong order!
How many times are you doing the copy?

chamster
10-01-2007, 05:28 AM
Once per sheet. Then i delete it and compute a new one, from new parameters. Then, copy the new one over to the other workbook (once only). All in all i will have 2 * 13 * 9 = 234 copies.

Also, what i've noticed was that all the computations i perform do work, as long as they are done in small chunks. As soon i get a loop to do it, there are errors (after 20 or so iterations). The impression i get, yes, i do know it sounds like i'm crazy, is that Excel gets overloaded and goes poo-poo.

rory
10-01-2007, 05:32 AM
Are you saving the workbook at regular intervals? You might want to take a look at the codename of the sheet to be copied when the copy fails. And also check the memory usage.
It doesn't sound that crazy - I've made Excel go 'poo-poo' many times...

chamster
10-01-2007, 05:44 AM
I'm not saving the workbook at all. Should i? Or rather, judging from your question, "i should, shouldn't i?", hehe.

Thanks.

Now, that i've tried to save every second copy occurence, i discovered to be great disappointment that the error is still occuring. At the same time also... Memory seems to be OK. Any more suggestions?

rory
10-01-2007, 06:14 AM
What's the codename of the sheet when it fails? Are you sure you can't post a sample - it would be a lot easier than trying to guess every conceivable possibility!

chamster
10-01-2007, 07:39 AM
I'm pretty sure it won't do us much good. The data is taken from a server for access to which you need to be physically at this office. I can't even connect from home, even though i have the code and permissions.

I'd give a smallest example but as you recall, the error occurs on the bigger scale only. I'm about to give it up and do it by hand. It will not be efficient but hey, who needs sleeping, right?

Supposing it's a memory issue (#7, i believe) - what could be done? I'm only getting error #1004 (i hate this number!) but perhaps it's still connected to the memory somehow. Suppose that it is. What does one do?