Macro Move Sheets

P

Packfan86

New Member

Joined

Jun 24, 2008

Messages

26

• Jul 23, 2008

• #11

It worked differently this time! But I get an error.

"Run-time error '1004':
Method 'Copy' of object '_Worksheet' failed

Then I hit debug and it highlights this line

ws.Copy

Any thoughts?

Thanks in advance!

 

Upvote 0

cornflakegirl

Well-known Member

Joined

Nov 4, 2004

Messages

2,023

• Jul 23, 2008

• #12

Is your workbook protected?

 

Upvote 0

P

Packfan86

New Member

Joined

Jun 24, 2008

Messages

26

• Jul 23, 2008

• #13

I don't think so, but it is saved in a shared drive, does that matter?

 

Upvote 0

cornflakegirl

Well-known Member

Joined

Nov 4, 2004

Messages

2,023

• Jul 23, 2008

• #14

Um, it might do. Can you save a test non-shared version, and see if the code works on that?

Can you manually copy a worksheet?

 

Upvote 0

P

Packfan86

New Member

Joined

Jun 24, 2008

Messages

26

• Jul 23, 2008

• #15

I tried two things: One I tried to save it as a .xlsm and that didn't work. Then I saved in on my desktop and ran the macro and it gave me the same error.

I can manually copy and paste each sheet into their own new book.

Any thoughts?

Thanks!

 

Upvote 0

cornflakegirl

Well-known Member

Joined

Nov 4, 2004

Messages

2,023

• Jul 23, 2008

• #16

Have you got hidden sheets?

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jul 23, 2008

• #17

At request of CG thought I'd wade in... I have a different approach which may / may not work... no doube less elegant than the one tried thus far.

This code should go in a standard module in your file with 80 sheets etc...

I have assumed file name is set in Cell A2 on each of the 80 sheets... put in a catch in case A2 is emtpy...

Code:

Sub lasw10test()
Dim wb1_full As String: wb1_full = ActiveWorkbook.FullName
Dim wb1 As String: wb1 = ActiveWorkbook.Name
Dim wb2 As String
Application.ScreenUpdating = False
For Each ws In ThisWorkbook.Worksheets
    wb2 = ws.Cells(2, 1).Value
    If wb2 = "" Then wb2 = ws.Name
    Workbooks.Add
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Replace(wb1_full, wb1, wb2)
    Application.DisplayAlerts = True
    Windows(wb1).Activate
    ws.Copy Before:=Workbooks(wb2).Sheets(1)
    Application.DisplayAlerts = False
    Sheets(2).Delete
    Application.DisplayAlerts = True
    ActiveWorkbook.Close SaveChanges:=True
Next ws
Application.ScreenUpdating = True
End Sub

EDIT: I only delete Sheets(2) as by default my files open with 1 worksheet as opposed to standard 3... if you don't mind leaving the empty sheets in the file you can do away with the delete (and display alerts etc)

CG's points re: protection hold true -- sheets/workbook can't be protected for this approach to work.

 

Last edited: Jul 23, 2008

Upvote 0

P

Packfan86

New Member

Joined

Jun 24, 2008

Messages

26

• Jul 23, 2008

• #18

Thank you all! I got it to work. There was a hidden sheet in my book that was causing the error. It works perfectly!

Thanks!

 

Upvote 0

cornflakegirl

Well-known Member

Joined

Nov 4, 2004

Messages

2,023

• Jul 23, 2008

• #19

Hey Luke - thanks for popping in!
I've not seen anyone take that approach before - are you idiosyncratic or brilliant?

(3 sheets - who thought that was a good idea?!)

 

Upvote 0

P

Packfan86

New Member

Joined

Jun 24, 2008

Messages

26

• Jul 23, 2008

• #20

Since both of you seem so knowledgeable about Excel. I have a question for you about PivotCharts. In 2003 there was a drop down filter box on the actual PivotChart. In 2007 that no longer exists. How do I get it to come back?

Thanks in advance!

 

Upvote 0