VBA - copy selected worksheets to new worksbooks

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that brings together data from a myriad of places and eventually puts together a number of worksheets one for each country that I am working with.

What I would like to be able to do more easily is to highlight all my country worksheets - [I can do this bit manually] and then save them as individual workbooks each called [TAB].xlsx ie United Kingdom.xls in the same folder as the current workbook.

Is this possible using VBA or some other means.

Currently each week, I am right clicking and copying the tab to a new workbook then saving, 32 times and its getting very laborious.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Without caffeine, but try:

Code:
Sub SaveAllSheets()

Dim wbk As Workbook
Dim wsh As Worksheet

Set wbk = ActiveWorkbook

For Each wsh In wbk.Worksheets

   wsh.Copy
   ActiveWorkbook.Close SaveChanges:=True, Filename:=wsh.Name & ".xls"
  
Next wsh

End Sub
 
Last edited:
Upvote 0
lol that's short sweet and works perfectly

It must have been the caffeine that I was missing :eek:

Cheers RedBeard

oops my previous note was too fast, where there are look ups in the sheet when its copied the links disappear so you end up with errors

I'm guessing wsh.copy is wrong it needs to be wsh.CopyValues or something
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top