Copy/Paste Filtered Data To New Sheet In New Workbook.

**ScotyB** · 05-10-2012, 06:29 PM

Greetings all,

I've searched the forum and found a few bits of code that would appear to do what I want but, after having tried several of them, I'm having no luck. In the code below, everything works fine except that it's currently set to PrintPreview for testing purposes. What I would like is to change the preview command so that it copies the filtered data to a sheet called "Transcribed" in a new workbook (Z:\MonthlyReport.xlsx) as a PasteSpecialALL so that the columns A to K have everything copied across - can someone help?

Please Login or Register  to view this content.

Cheers,

AJ

**Charles** · 05-10-2012, 08:03 PM

ScotyB,

I added some code to yours.
I set the copy range from column 1 to column 8.
I have it copy data if more that 1 record.
You may have to play with it using your data.
Hope this helps.

Please Login or Register  to view this content.

**ScotyB** · 05-10-2012, 08:20 PM

Thanks, Charles, I'll try that code in a moment; I'm curious though - if my filtered range consists of 11 columns (A to K), shouldn't the code you added reflect the same number of columns?

Cheers,

AJ

**ScotyB** · 05-10-2012, 08:22 PM

Charles,

I just tried it and got a runtime error 91 - Object variable or with block variable not set - the debugger yellows this line in the code

Please Login or Register  to view this content.

Cheers,

AJ

**Charles** · 05-11-2012, 11:41 AM

ScoyyB,

My error change all Rng1 to Rng. As for the column if you look at this line of code:
I have the column set from column "A" to column "I" change "8" to "11". This will now be A-K.

Please Login or Register  to view this content.

**ScotyB** · 05-11-2012, 06:31 PM

Charles,
thanks for taking another look however, the same problem occurred (run-time 91) and the yellowed line was the same. I tried changing the range from A65536 to A:K but this produced the same error and when I tried changing ActiveSheet to Sheets ("Data"), I got a 'Data Overflow' error on the MyVal = Sheets ("Data"), etc line of code - I've put the code back here so you can see where it's at...

Please Login or Register  to view this content.

**Charles** · 05-12-2012, 01:44 PM

ScotB,

I modified the code see if this works. However if it does not could you attach a copy of the workbook?

Please Login or Register  to view this content.

**ScotyB** · 05-12-2012, 09:09 PM

Charles,

no luck, I'm afraid; it brought up a different error this time so, as requested, I've attached my workbook. To see what happens, click the "Print Monthly Activities" button on the front page, select two dates from the user form that appears next, and then click the pink button in the middle of the form.

Thanks again for your time and patience.

Cheers,

AJ

**Charles** · 05-13-2012, 01:25 PM

ScotyB,

I modified your code and added remarks.

Please Login or Register  to view this content.

**ScotyB** · 05-13-2012, 09:10 PM

Charles,

when I first tried your new code, I got a 'subscript out of range' error before I realised that I didn't have the other workbook (Monthly Report) open - as soon as I opened it and tried the code again, bingo!, everything worked a treat. I'll just add a little code myself to make sure that the other workbook is open and since I use Excel 2010, I think I'll have to change the Range("A65536") to Range("A" & Rows.Count) which should cover me for all versions of Excel, or so I've been told, lol.

Again, many, many thanks for all your time and effort, Charles, it's greatly appreciated.

Cheers,

AJ