Copy and paste problems - excel 2010 :(((

[grphillips]

I am new to office 2010.

I cant copy and paste specific selected filtered cells columns and rows from 1 excel workbook and worksheet to a brand new excel instance and worksheet.

(1) I need to keep the same formatting and column width and all cells need to be editable. Surely it cant be that difficult to keep the same format and columns widths at same time. what is the method either to do straight away or workaround ?

(2) Also, when i opened a new excel instance to paste in and clicked paste special it would come up with paste link icon not the normal paste values and other options like transpose etc. others times it would show normally.

Please help urgently spent 4 hours suffering on this today. left work feeling very ineffective and frustrated.

Thanks,

[xladept]

Hi grphillips,

Something like this carries over the formats:

Sub SpecCP(): ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy Range("A11"): End Sub

[grphillips]

Thank you, in need of urgent help, please help. Are you saying this can only be achieved via code?

why A11.

whats about column widths and format and data for a true copy and paste etc.? Isn't there simple steps?

This is not full code as i will need to paste in a new spreadsheet?

[xladept]

Hi grphillips,

The "A11" is only there for want of a better address.

A "true" copy - paste?

Sub SpecCP(): ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy: Range("A11").PasteSpecial: End Sub

I don't know if you could do this with formulas - have you been doing it manually??

[grphillips]

Thank you again, sorry I thought the art of copying and pasting in all scenarios was simple.
Its proving cumbersome or just not working. I was suprised to se a code solution.

What does the code above do?I have no idea if the code works straight off sorry very green.
I would like to pasting only the filtered values in selected rows and range whatever the used range - could be any number but need it to work. I simply ant to past in a new spreadsheet with everything same format, selected data, column widths etc,

I tried manually with problems never sure of best method. It never pastes with column widths. Also, paste special icon behaves strangely and sometime the only option/dialog box is to past link rather than the normal options like paste value etc. basicaly the dialog box is changes quit often when i want to paste in a new spreadsheet.

[grphillips]

Thank you again, sorry I thought the art of copying and pasting in all scenarios was simple.
Its proving cumbersome or just not working. I was suprised to se a code solution.

What does the code above do?I have no idea if the code works straight off sorry very green.
I would like to pasting only the filtered values in selected rows and range whatever the used range - could be any number but need it to work. I simply ant to past in a new spreadsheet with everything same format, selected data, column widths etc,

I tried manually with problems never sure of best method. It never pastes with column widths. Also, paste special icon behaves strangely and sometime the only option/dialog box is to past link rather than the normal options like paste value etc. basicaly the dialog box is changes quit often when i want to paste in a new spreadsheet.

[xladept]

Hi grphillips,

The filtered values are the ones that are still visible - so this little routine just copies those visible (filtered) rows and pastes only them wherever you want - it's a big boon to deduplication etc

[grphillips]

Thank you again, still confused sorry.

Are you saying this is not simple then in that it can be pasted manually through simple steps?What are the most effective steps if i can be achieved manually in 2010.

Does it paste also columns widths and everything per my post?

How does it know where to paste too?

How does it know where to paste from?

I have never seen vba presented like this with colon and 2 brackets.

Is this a new layout form 2010 onwards?

Sub SpecCP():

[grphillips]

Thank you again, still confused sorry.

Are you saying this is not simple then in that it can be pasted manually through simple steps?What are the most effective steps if i can be achieved manually in 2010.

Does it paste also columns widths and everything per my post?

There are heading text like doucument numbers etc which are not part of the filter this will be visbale but not part of filter.

How does it know where to paste too?

How does it know where to paste from?

I have never seen vba presented like this with colon and 2 brackets at start. Is this a new layout form 2010 onwards?

Sorry i am so green, i don't even know steps how to paste this so this macro appears on any opened spreadsheet as part of initialisation and i can run as when i need or created as a button.

Thanks.

[xladept]

you can use the colon to signify a new line - i'll get back to you tomorrow - where do you want to paste - bookname,sheetname,range???

[grphillips]

Getting somewhere.run my first macro.

This seems to paste in a11 in same active sheet on trial run.

(a)I need it to paste in new worksheet i.e sheet 2 which may not exist and need to be auto created.
(b) or i need to paste new excel workbook and sheet 1>

Can you post some examples of how this can be achieved flexibly


Thank you very much

[grphillips]

I am dying in work with this simple activity very tiring and its 2am here((, please save me from this grift much appreciated.


Basically i am looking for ultimate flexibility where i paste without messing around rezizing coolumns and row width etc and format,

Please post various situations and code or if there is 1 code that does it all please standardize.

See previous post, thanks for patience

[grphillips]

There is no standard book name it either the active sheet can be sheet 2 or new excel workbook.

I am open to standard flexible copy and paste solutions better and quicker than junk on office 2010 front end
that isn't fit for purpose as you have to mess around far too much adjusting and there seems to be bugs with paste special icons.

Please kindly put me out my misery here.

This is for big spreadsheets around 2000 lines.


Thank you.

[xladept]

Manana - I'll rewrite the routine tomorrow - sorry

[xladept]

Hi grphillips,

Here's a routine that might work for you:

Sub SpecCP()                    'Run this once you've done your filtering
Dim wb As Workbook, w1 As Worksheet, w2 As Worksheet
Set wb = ActiveWorkbook: Set w1 = wb.ActiveSheet
For Each w2 In wb.Worksheets
If w2.Name = "Sheet2" Then GoTo Setw2
Next        'If you want it started at a different range then change the "A1"
Worksheets.Add.Name = "Sheet2"
Setw2: w2 = wb.Sheets("Sheet2"): w1.Activate: w2.Cells.ClearContents
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Copy: w2.Range ("A1")
w2.Range("A1").PasteSpecial xlPasteColumnWidths
End Sub

Directions for running the routine(s) just supplied

Copy the code to the clipboard

Press ALT + F11 to open the Visual Basic Editor.

Open a macro-enabled Workbook or save your Workbook As Macro-Enabled

Select “Module” from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name