Extracting and exporting to new workbook

[Lifeseeker]

Hi there,

I have a partially working file, and basically what I would like to do is to copy the filtered rows and paste them into a new workbook. (you specify a reporting quarter and hit the button to extract)

Please see the attached file.

When I run the code, it has 1 problem:
1) it hides the original rows in the original workbook. (I need the original to stay the way it was)

Could someone point me in the right direction?

Thank you

[arlu1201]

I found an issue in this code in your file -

Please Login or Register  to view this content.

It says

Please Login or Register  to view this content.

but there is no workbook which is created to be called as NewWorkBook. Hence whatever earlier tasks (before this line) it performs, are done on the original workbook.

[Lifeseeker]

Hi,

After I changed the worksheet in the new workbook to the actual default name, "Sheet1", I am beginning to get a different error.

And the autofilter on the original sheet still doesn't cancel once the extraction begins. I would like to have it go back to its original state so that we don't have to manual cancel the filter every time we go back.

Thank you

New file is attached as well.

[Lifeseeker]

Anybody has any input to this one?

It seems that the concept is simple, which is just hiding the filtered rows, but I can't get the code in there.

Code used is:

Please Login or Register  to view this content.

Thank you

[tigeravatar]

Lifeseeker,

One way:

Please Login or Register  to view this content.

[Lifeseeker]

Hi Tiger,

It worked out perfectly.

What's wrong with my original code?

Few follow-up questions for your code:
1) what does the (1) represent?
2) What's the "A" in Cells(Rows.Count, "A")
3) .Offset(1).EntireRow.Delete won't actually delete the rows in the original workbook?

By the way, I had originally intended to just extract cells from column A B C and I actually don't want to see the button and the filter and stuff in the new workbook.

Sorry I didn't make this point clear before.

Thank you

[tigeravatar]

1. Sheets(1) represents the first worksheet in the workbook
2. "A" is the column letter
3. Right, because we're performing the operation on wbNew and not wbOriginal

I can provide code so that only cols A:C appear in the new workbook, but will have to be tomorrow

[tigeravatar]

Lifeseeker,

This method will only bring columns A:C

Please Login or Register  to view this content.

[Lifeseeker]

Hi,

In the following code segment:

What does this do?

Please Login or Register  to view this content.

How is the UsedRange defined? Any range of cells that has values in it? So in this case, col A B and C?

and what does the following code do?

Please Login or Register  to view this content.

Are we moving ws2 to somewhere or?

And if the following code segment is where we apply the filter, then why is it being done on ws2?

Please Login or Register  to view this content.

Thank you

[tigeravatar]

Lifeseeker,

What does this do?

Please Login or Register  to view this content.

That creates a new worksheet in the original workboook.

How is the UsedRange defined?

.UsedRange is a worksheet object property. The used range is the range of cells defined by the first populated cell in the upper left to the last populated cell in the lower right of a worksheet. The Intersect method I used with it limits that range to just the populated cells in columns A, B, and C. See the Excel Help (F1) for more information on .UsedRange and Intersect

and what does the following code do?

Please Login or Register  to view this content.

The .Move with no arguments afterwards moves ws2 into a new workbook.

And if the following code segment is where we apply the filter, then why is it being done on ws2?

Please Login or Register  to view this content.

Earlier in the code we copied over columns A, B, C into ws2. Since ws2 now contains that information, we can perform the filter on ws2 and delete unnecessary rows, leaving the original data untouched and unfiltered as desired. Then ws2 is moved into a new workbook so that the extracted data is in a new workbook as requested.

[Lifeseeker]

Thank you Tiger. Logic is very clear.

[tigeravatar]

You're very welcome and happy coding