Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Run-Time Error '1004' (Copying worksheets)

  Asked By: Kerri    Date: Oct 27    Category: MS Office    Views: 1780
  

I'm having trouble copying worksheets. I've tried both copying them
both before/after various worksheets, but nothing is fullproof. I've
also tried saving my workbook periodically during the run (through
loops), this also is not fullproof.

So, I was wondering, is there any other remedy?

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Dustin Dean     Answered On: Oct 27

Below is the code I used to place a worksheet into a workbook from the
field...

I took our template we have open that...(workbook from field is already
open)

I copied from the template to the workbook (oTempSheet.Copy oSheet)...
When I space bar after oSheet I get the option of Before/After... But
the way I did it it defaults to the very beginning...

Be careful, I found no way to remove links because no matter what I did
the cell formulas in the workbook always gave the path and file name of
the template... So I had to write the code to put the formula in the
cell correctly...

Does this help??

Dim oTemplate As Object
Dim oTempBook As Excel.Workbook
Dim oTempSheet As Excel.Worksheet
Dim oTempCell As Excel.Range

'Doing Stuff
Set oTemplate = VBA.GetObject("", "Excel.application")

'Doing Something Else
Set oTemplate = VBA.GetObject("J:\Forecast\2005-Q3 Quarterly
Forecast.xlt")

'Doing Something Else
Set oTempBook = oTemplate

'Saving resources
oBook.Application.Visible = True
oBook.Parent.Windows(1).Visible = True
oTempBook.Application.Visible = True
oTempBook.Parent.Windows(2).Visible = True

'Q3 Forecast Template
Set oSheet = oBook.Worksheets("JULY Payroll")

oTempBook.Activate
Set oTempSheet = oTempBook.Worksheets("Quarterly Summary")
oTempSheet.Activate
oTempSheet.Cells.Select
oTempSheet.Copy oSheet

Set oTempSheet = oTempBook.Worksheets("Workbook Info")
oTempSheet.Activate
Set oTempCell = oTempSheet.Range("A5:B5")
oTempCell.Select
oTempCell.Copy

'Jump Back to the senders book
oBook.Activate
Set oSheet = oBook.Worksheets("Workbook Info")
oSheet.Activate
Set oRange = oSheet.Range("A5:B5")
oRange.Select
oRange.Insert shift:=xlDown

oSheet.Range("B5").Select
oSheet.Names.Add Name:="AHMCO_NAME", RefersToR1C1:="=Workbook
Info!R5C2"

'Upgrade the Version Number
oSheet.Range("AHMCO_VERSION").Value = "1.24"
oSheet.Range("A1").Select
oSheet.Range("A1").Activate


oTempBook.Close savechanges:=False

'Acitivate the Sent Book
oBook.Activate

 
Didn't find what you were looking for? Find more on Run-Time Error '1004' (Copying worksheets) Or get search suggestion and latest updates.




Tagged: