Advertisement

04.14.2008 at 10:06AM PDT, ID: 23320985 | Points: 500
[x]
Attachment Details
Macro needed to copy worksheet and increment worksheet name
Zones: Microsoft Excel Spreadsheet Software, General Office Suite Software, Visual Basic Programming
Gurus,

I need a macro that copies an existing worksheet and increments the name.

For example, I have an existing worksheet (with formatting - column widths) called Pg20.

I need to add worksheets Pg20 to Pg60 - all with the same formatting.

Can anyone help?

Thanks in advance!

Steve
Start your free trial to view this solution
Question Stats
Zone: Software
Question Asked By: SStroz
Question Asked On: 04.14.2008
Participating Experts: 1
Points: 500
Views: 0
Translate:
Loading Advertisement...
04.14.2008 at 10:30AM PDT, ID: 21351870

Rank: Genius

matthewspatrick:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
04.14.2008 at 01:44PM PDT, ID: 21353702
SStroz:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
05.09.2008 at 09:35AM PDT, ID: 21534496

Rank: Genius

matthewspatrick:

All comments and solutions are available to Premium Service Members only.

Start your 7 day free trial and see for yourself why Experts Exchange is the easiest and most proven technology resource in the world. Get Started

Already a member? Login to view this solution.

 
 
Loading Advertisement...
Microsoft
  • Internet Protocols
  • Applications
  • Development
  • OS
  • Hardware
  • Windows Security
Apple
  • Operating Systems
  • Hardware
  • Programming
  • Networking
  • Software
Internet
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Spy / Ad Blockers
  • Web Browsers
  • New Net Users
  • Web Development
  • Chat / IM
  • Anti Spam
  • Web Servers
  • Anti-Virus
  • Email Clients
Gamers
  • Tips
  • Online / MMORPG
  • Puzzle
  • Emulators
  • Action / Adventure
  • Role Playing
  • Consoles
  • Game Programming
  • Strategy
  • Sports
  • Misc
  • Computer Games
Digital Living
  • Hardware
  • New Net Users
  • New Users
  • Software
  • Digital Music
  • Gaming World
  • Home Security
  • Apple
  • Networking Hardware
Virus & Spyware
  • Vulnerabilities
  • IDS
  • Encryption
  • Anti-Virus
  • Operating Systems Security
  • Software Firewalls
  • WebApplications
  • Cell Phones
  • Operating Systems
  • Internet
  • Hardware Firewalls
Hardware
  • Handhelds / PDAs
  • Displays / Monitors
  • Components
  • Networking Hardware
  • Peripherals
  • Laptops/Notebooks
  • Storage
  • Servers
  • Desktops
  • New Users
  • Misc
  • Apple
Software
  • System Utilities
  • Industry Specific
  • Network Management
  • Photos / Graphics
  • Page Layout
  • VMWare
  • Misc
  • Web Development
  • OS
  • CYGWIN
  • Voice Recognition
  • Message Queue
  • Quality Assurance
  • Security
  • Firewalls
  • MultiMedia Applications
  • Development
  • Database
  • Office / Productivity
  • Business Management
  • OS/2 Apps
  • Server Software
  • Internet / Email
ITPro
  • OS
  • Storage
  • Encryption
  • Operating Systems Security
  • Apple Hardware
  • Laptops & Notebooks
  • Servers
  • Networking Hardware
  • Peripherals
  • Devices
  • Displays / Monitors
  • WebTrends / Stats
  • Search Engines
  • Firewalls
  • WebApplications
  • IDS
  • Vulnerabilities
  • Email Clients
  • File Sharing
  • Spy / Ad Blockers
  • Web Browsers
  • Web Servers
  • Networking
  • Anti-Virus
  • Chat / IM
  • Anti Spam
Developer
  • Web Servers
  • Web Browsers
  • Game Programming
  • Dev Tools
  • Industry Specific
  • Office / Productivity
  • Database
  • CYGWIN
  • Web Development
  • Search Engines
  • File Sharing
  • WebTrends / Stats
  • Programming
  • Content Management
  • Application Servers
  • Protocols
Storage
  • Removable Backup Media
  • Storage Technology
  • Servers
  • Grid
  • Remote Access
  • Backup / Restore
  • Misc
  • Hard Drives
OS
  • Miscellaneous
  • Security
  • Development
  • Linux
  • VMWare
  • MainFrame OS
  • Unix
  • Apple
  • OS / 2
  • AS / 400
  • BeOS
  • Microsoft
  • VMS / OpenVMS
Database
  • Oracle
  • Miscellaneous
  • MySQL
  • Software
  • Sybase
  • Contact Management
  • PostgreSQL
  • Data Manipulation
  • Clarion
  • InterSystems Cache
  • Siebel
  • MUMPS
  • OLAP
  • SQLBase
  • SAS
  • GIS & GPS
  • 4GL
  • Berkeley DB
  • DB2
  • Informix
  • Interbase / Firebird
  • FoxPro
  • Reporting
  • LDAP
  • Filemaker Pro
  • MS SQL Server
  • dBase
  • MS Access
Security
  • Misc
  • Web Browsers
  • Software Firewalls
  • Operating Systems Security
  • File Sharing
  • Spy / Ad Blockers
  • Vulnerabilities
  • WebApplications
  • IDS
  • Anti-Virus
  • Encryption
  • Anti Spam
  • Email Clients
  • VPN
  • Chat / IM
Programming
  • Editors IDEs
  • Installation
  • Handhelds / PDAs
  • Multimedia Programming
  • System / Kernel
  • Algorithms
  • Game
  • Signal Processing
  • Project Management
  • Open Source
  • Database
  • Misc
  • Languages
  • Processor Platforms
  • Theory
Web Development
  • Scripting
  • Blogs
  • Web Servers
  • Software
  • Search Engines
  • Web Graphics
  • Images
  • Internet Marketing
  • Images and Photos
  • Components
  • Document Imaging
  • Web Languages/Standards
  • Illustration
  • WebApplications
  • Fonts
  • WebTrends / Stats
  • Authoring
  • Digital Camera Software
  • Miscellaneous
Networking
  • Protocols
  • Apple Networking
  • Network Management
  • Message Queue
  • Application Servers
  • Content Management
  • File Servers
  • Email Servers
  • Misc
  • Java Editors & IDEs
  • Wireless
  • Networking Hardware
  • Backup / Restore
  • System Utilities
  • ISPs & Hosting
  • Web Servers
  • Storage Technology
  • Removable Backup Media
  • Servers
  • Broadband
  • Grid
  • OS / 2
  • Novell Netware
  • Unix Networking
  • Windows Networking
  • Security
  • Telecommunications
  • Operating Systems
  • Linux Networking
Other
  • Community Advisor
  • Lounge
  • Community Support
  • New Net Users
  • Philosophy / Religion
  • Math / Science
  • Miscellaneous
  • URLs
  • Expert Lounge
  • Politics
  • Puzzles / Riddles
Community Support
  • Suggestions
  • New to EE
  • New Topics
  • Community Advisor
  • CleanUp
  • Announcements
  • General
  • Feedback
  • Input
  • EE Bugs
 
04.14.2008 at 10:30AM PDT, ID: 21351870

Rank: Genius

matthewspatrick:
Sub MakeWs()

    Dim Counter As Long
    Dim Ws As Worksheet
    Dim NewWs As Worksheet

    Set Ws = ThisWorkbook.Worksheets("Pg20")
    For Counter = 21 To 60
        Set NewWs = Ws.Copy(After:=Ws)
        NewWs.Name = "Pg" & Counter
    Next

    MsgBox "Done"

End Sub
 
04.14.2008 at 01:44PM PDT, ID: 21353702
SStroz:
matthewspatrick,

Thanks for your ideas/code.  When I run it, I get the following error:

Compile Error:
Expected Function or variable

When I click OK the following is highlighted:

".Copy"

Here was my code:

Sub MakePages()
'
' MakePages Macro
' Macro recorded 4/14/2008 by Steve Stroz
'

    Dim Counter As Long
    Dim Ws As Worksheet
    Dim NewWs As Worksheet

    Set Ws = ThisWorkbook.Worksheets("Pg7")
    For Counter = 8 To 70
        Set NewWs = Ws.Copy(After:=Ws)
        NewWs.Name = "Pg" & Counter
    Next

    MsgBox "Done"

'
End Sub


Any ideas????

Steve
 
05.09.2008 at 09:35AM PDT, ID: 21534496

Rank: Genius

matthewspatrick:
SStroz,

Sorry for taking so long to reply :(

Try replacing:

        Set NewWs = Ws.Copy(After:=Ws)
        NewWs.Name = "Pg" & Counter

with:

        Ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = "Pg" & Counter

Regards,

Patrick
 
 
20080236-EE-VQP-29 / EE_QW_2_20070628