Save a named sheet from one workbook to a new workbook in the same folder

Question

I would like to be able to

take a sheet called "data" in a given workbook called "original_data.xlsm",
copy it conditionally (using Autofilter or something else), say only those rows where column C was "dog"
Create a new workbook, in the same folder as the original book, called dog.xlsm and save the copied stuff into a new sheet called "dog data".
Then repeat with a different filter.So for example copy and autofilter if column C was "cat" and create a workbook "cat.xlsm", in the same folder as the original book, with a sheet called "cat_data" containing some filtered data.

I've been making incorrect attempts for three days now and would appreciate some help. Here is what I have done so far.

Workbooks.Add
Set wb = ActiveWorkbook
GetBook = ActiveWorkbook.Name
wb.Sheets("data").SaveAs Workbooks(GetBook).Path & "\dog" 

Workbooks("dog.xlsx").Worksheets("Sheet1").UsedRange.AutoFilter Field:=3, Criteria1:="=dog"

But it's not working. :(

[This](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) will get you started — Siddharth Rout, Nov 19 '13 at 16:53
@RBarryYoung The macro is in some third workbook. Ok.. I see where this is going — Amatya, Nov 19 '13 at 17:01
@Sam It's creating some empty workbooks. Not copying the data. — Amatya, Nov 19 '13 at 17:03
@Amatya I don't see anywhere in your code where you are trying to copy data. Which line are you expecting the data to copy? (maybe you should record a macro doing what you want, then look at the code) — Sam, Nov 19 '13 at 17:09
@Sam.. oops I forgot to write the command in the quesion. I'll fix itnow. — Amatya, Nov 19 '13 at 18:57

DaveU · Accepted Answer · 2013-11-29T23:17:45.417

2

Looks like you're trying to set wb to "original_data.xlsm", but your first line is making the new workbook the active workbook.

Workbooks.Add
Set wb = ActiveWorkbook

See if this helps.

Sub sheetCopy()
    Dim wbS As Workbook, wbT As Workbook
    Dim wsS As Worksheet, wsT As Worksheet

    Set wbS = ThisWorkbook 'workbook that holds this code
    Set wsS = wbS.Worksheets("Data")

    wsS.Copy
    Set wbT = ActiveWorkbook 'assign reference asap

    Set wsT = wbT.Worksheets("Data")
    wsT.Name = "Dog Data" 'rename sheet

    wbT.SaveAs wbS.Path & "\dog.xlsx" 'save new workbook
    wsT.UsedRange.AutoFilter Field:=3, Criteria1:="=dog"
End Sub

edited Nov 29 '13 at 23:17

answered Nov 19 '13 at 22:43

DaveU

1,082
2
14
25

1

Thanks a lot!! I absolutely fear using activeworkbook and activeworksheets cuz I can't keep track of what's active. I am working between 4 different workbooks with 6 sheets each and it's a kind of a mess. Would you be able to tell me how to do this without referencing an active workbook.. maybe using the full name each time? Thanks! – Amatya Nov 29 '13 at 20:41
1

You're welcome. And you're quite right to stay away from activeworkbook/activesheet as much as possible, and use direct references instead - I've modified my answer to illustrate some examples. Note: the line following `wsS.Copy` is one of the few instances where `ActiveWorkbook` is unavoidable. – DaveU Nov 29 '13 at 23:22

Save a named sheet from one workbook to a new workbook in the same folder

1 Answers1