Solved: Copy selected filtered sheets to a new workbook

[aloy78]

Hi,
This is another report that I need to generate. In general I need to copy two sheets over to a new workbook. There are many threads on this topic but not exactly what I want to achieve. I only need the filtered values and selected columns to be copied. I've attached a file to describe what I'm trying to achieve. Makes explanation a lot easier :)

[mancubus]

is this file different from the file in your previous thread?


http://www.vbaexpress.com/forum/showthread.php?t=38699

[aloy78]

is this file different from the file in your previous thread?


http://www.vbaexpress.com/forum/showthread.php?t=38699

Hi mancubus,
Yeah, it different from that. That file was to copy the filtered range and combine them into a single sheet.

Where as for this one is to copy the the filtered values only(remove filters, links and codes). Only selected sheets to be copied over to the new workbook.

[mancubus]

see attached...
check (and change, if necessary, in the code) columns to delete.
no data in col U!

[aloy78]

see attached...
check (and change, if necessary, in the code) columns to delete.
no data in col U!

Hi mancubus,
What do you mean by "no data in col U!". Does that mean that I cannot put anything there?

Oh yeah, about this small remark you put in:
Application.SheetsInNewWorkbook = 3 ' change to suit

What does that mean :think:

Apart from that, the code works perfect.:yes

[mancubus]

Hi mancubus,
What do you mean by "no data in col U!". Does that mean that I cannot put anything there?

Oh yeah, about this small remark you put in:
Application.SheetsInNewWorkbook = 3 ' change to suit

What does that mean :think:

Apart from that, the code works perfect.:yes

hi.

i mean your data are on columns A thru T.
sure you can.
the code provided deletes columns G thru P.
so if your data table changes, i mean if you add columns etc, you shoud change the line that deletes the columns where necessary.

Columns("G:P").Delete




this line makes new blank workbooks open with single worksheet:
Application.SheetsInNewWorkbook = 1
this is equal to manually changing the worksheet number:
options -> general -> include this many sheets: 1


this line opens blank workbook with single worksheet:
Set nwb = Workbooks.Add


this line makes new blank workbooks open with 3 worksheets:
Application.SheetsInNewWorkbook = 3 ' change to suit

this is equal to manually changing the worksheet number:
options -> general -> include this many sheets: 3
you may change the number 3 ifyou like.
or if your standard is 1 worksheet, then you may delete these two lines other than Workbooks.Add from code.

[aloy78]

hi.

i mean your data are on columns A thru T.
sure you can.
the code provided deletes columns G thru P.
so if your data table changes, i mean if you add columns etc, you shoud change the line that deletes the columns where necessary.

Columns("G:P").Delete




this line makes new blank workbooks open with single worksheet:
Application.SheetsInNewWorkbook = 1
this is equal to manually changing the worksheet number:
options -> general -> include this many sheets: 1


this line opens blank workbook with single worksheet:
Set nwb = Workbooks.Add


this line makes new blank workbooks open with 3 worksheets:
Application.SheetsInNewWorkbook = 3 ' change to suit

this is equal to manually changing the worksheet number:
options -> general -> include this many sheets: 3
you may change the number 3 ifyou like.
or if your standard is 1 worksheet, then you may delete these two lines other than Workbooks.Add from code.

hi mancubus,
Thanks for the explanation :)

Okay just to summarize. So if I'm gonna export 5 sheets out to that new workbook. Then I will have to amend as below right?


Application.SheetsInNewWorkbook = 5 ' if I have 5 sheets to copy over to that new workbook


Gosh, vba codes are so cool. :yes

[mancubus]

hi mancubus,
Thanks for the explanation :)

Okay just to summarize. So if I'm gonna export 5 sheets out to that new workbook. Then I will have to amend as below right?


Application.SheetsInNewWorkbook = 5 ' if I have 5 sheets to copy over to that new workbook

Gosh, vba codes are so cool. :yes
wellcome.

no.
it's not related with copying worksheets to another workbook.
you may copy 100 (or more) worksheets to a blank workbook that contains only 1 blank worksheet.

i have a standard for my reports or summary reports in excel workbook format: my reports do not contain blank worksheets.
so I added these lines, by habit :dau: , to make your report not contain blank worksheets.


Application.SheetsInNewWorkbook = 1
Set nwb = Workbooks.Add
Application.SheetsInNewWorkbook = 3 ' change to suit
awb.Activate
Sheets(Array("Import-Sea", "Import-Air")).Copy Before:=nwb.Sheets(1)
Application.DisplayAlerts = False
nwb.Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True

so if you do not care if your report contains blank worksheets, delete the red lines above from macro. remaining lines will be:

Set nwb = Workbooks.Add
awb.Activate
Sheets(Array("Import-Sea", "Import-Air")).Copy Before:=nwb.Sheets(1)

[aloy78]

hi mancubus,
i finally get the picture :) Yeah I had that same habit of deleting blank sheets in a workbook. And for this, we have solved another vba mystery. hehehe...