Paste to visible cells only

[lichfields]

I'm trying to copy data from column A in a filtered list to the next column B. As column B is of course also filtered, I want to copy the visible cells (no problem) but then paste them to the visible cells (problem).

I've checked through earlier questions on this subject to find an answer but it seems there isn't one - at least not a straightforward one.

I did find a possible solution in the form of a "Code" as follows:

Worksheets("Sheet Name").Activate
Range("A2").Select
Selection.Copy
Range("A3:N1000").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Is this a macro thing? If so, how do you use it? I'm afraid I'm a macro virgin

Can anyone help?

 

[emilis]

The problem with this solution comes when you attempt to paste values into a filtered table. Excel pastes in a contiguous block, ie does not skip the lines that are filtered out, so you end up pasting your data into wrong rows.

You can put in a formula setting a value to the same as the source column, and copy this through but you need to remove the filter before doing copy-paste values. For one off situations this is fine but when your working with data that is filtered by multiple columns and a large amount of data it's very time consuming. Especially with many vlookup fields from other files. Each filter on and off takes time.

I scoured the forums and have worked out a solution that allows the user to select a portion of a column and copy it's contents as values to another column. So I basically emulate copy and paste on a per cell level and then go to the next visible cell.

First declare a global variable to store the source, I have used a macro to store the highlighted area as source, then a second to do the rest.


'**************************************
'Code starts here ---------------------------------------
Global dataSrc


'-------highlight the source cells then run this macro
'------- set source -------------------
Sub FilteredCopyValues()
' Step 1 of copy and paste values for filtered list of data
' 15/08/2012 - Ron
' uses a global variable to store the source range for copy and paste operation to be used in FilteredPasteValues


dataSrc = Selection.Address
End Sub

'------------ click on the first cell in the column you wish to paste into and run this macro
Sub FilteredPasteValues()
' Step 2 of copy and paste values for filtered list of data
' 15/08/2012 - Ron
' uses a global variable to store the source range for copy and paste operation to be used in FilteredPasteValues
' Select a range based on dataSrc
' select only visible cells
' Start at nominated destination starting cell then incrementally move down through visible rows populating with the source
' Can copy from non-filtered to filtered or from filtered to filtered range


Dim c As Range
Dim y As Long

y = 0
dataDest = Selection.Address
Range(dataSrc).SpecialCells(xlCellTypeVisible).Select

For Each c In Selection.Cells
If Range(dataDest).Offset(y, 0).RowHeight = 0 Then
Do
y = y + 1
Loop While Range(dataDest).Offset(y, 0).RowHeight = 0
End If
Range(dataDest).Offset(y, 0).Formula = c.Value
y = y + 1
Next c
End Sub


'******************************************

Any questions, please ask

Ron

Hi Ron,

I'm trying to run your second macro after doing these steps:
1. Copied cells from a column in a filtered list from 1st worksheet.
2. Run first macro.
3. Filtered list in 2nd worksheet. Put cursor in the first cell of column where copied cells will go.
4. Run second macro.

Then looking at results, there is a highlighted column of cells with no data in it, and it's not the column where I put the cursor in Step 3.

I'm wondering if the problem is in the Global declaration. I came across an error when I declared dataSrc the way you suggested. So instead, I typed Public dataSrc, which seemed to get rid of the error.

I'm new to macros using Excel 2010. So I'm not sure where I'm going wrong.

Any help is appreciated. Thanks!

 

[rondeondo]

Hi Emils

I have only tried this macro within one sheet. If you were to include code to allow reference by worksheet or workbook this could be made to work between separate sheets/books. I think this will be what is causing the error with the variable declaration.

If I'm wanting to copy between sheets, I just use an ordinary copy from the source to an empty spot on the destination sheet then use the macro to copy into the filtered list on the destination sheet.

The other thing to point out is that when selecting where to paste to you don't highlight the range but select just the first cell at the top of the column.

also the macro is only set up to do one column at a time. If you select multiple columns the paste will put the data all into one column.

I hope this helps

Ron

 

[rondeondo]

Hi Maxim

You are quite correct.

the other thing I didn't mention is that it will only work on one column at a time. This is what I needed so I didn't make it able to deal with a block of data.

Better explanation of usage:

There are two macros, one to select the source and the second to select the destination and do the copy and paste.
1: highlight the column of cells to be copied
2: run the FilteredCopyValues macro. There will be no sign of anything happening from this one3: click into the first cell of the colum to copy your data into, do not highlight the whole column
4: run FilteredPasteValues macro

You should see the pasting happen.

I've improved the macro a little since I initially share it, I have now included a line to turn off auto calculation at the start then turned auto calculate back on at the end. I found this sped up the runtime dramatically with a large file as it was previously re-calculating after each cell.

 

[rondeondo]

Hi Maxim_G

The use of the macro doesn't require that you select visible cells only, just highlight the range you want to copy. the macro steps through the range cell by cell and skips the hidden/filtered out cells, the first macro requires a simple range.

Hope this helps
Ron

 

[rondeondo]

Thanks Ron,

I also found another way to do it if anyone is interested:

Select the cells to copy, run this code:

Sub Copy_Filtered_Cells()
Set from = Selection
Set too = Application.InputBox("Select range to copy selected cells to", Type:=8)
For Each Cell In from
Cell.Copy
For Each thing In too
If thing.EntireRow.RowHeight > 0 Then
thing.PasteSpecial
Set too = thing.Offset(1).Resize(too.Rows.Count)
Exit For
End If
Next
Next
End Sub

That's a neater way of doing it and it gets around having two separate macros.
There are always many ways to achieve the same thing.


My apologies for multiple replies, I replied to the last response before I saw the others.

Ron

 

[emilis]

That's a neater way of doing it and it gets around having two separate macros.
There are always many ways to achieve the same thing.


My apologies for multiple replies, I replied to the last response before I saw the others.

Ron

Hi Ron,

Thanks for your quick response. Once I ran the two macros on source and destination cells within the same worksheet, it worked perfectly.

Best,
Emil

 

[Petille]

Hello!
Thank you so much for helping those of us struggling!

I ran the macro and it worked beautifully. Is there anyway to tweak it so that it copies values only (not the format).

My destination column is filtered by colour

Since the origin column format is very different, once the values are pasted the destination cells becomes the colour of the origin column.
Dragging the paint format tool modifies the hidden rows so i cannot fix the problem that way

Thanks!!

 

[rondeondo]

Hello!
Thank you so much for helping those of us struggling!

I ran the macro and it worked beautifully. Is there anyway to tweak it so that it copies values only (not the format).

My destination column is filtered by colour

Since the origin column format is very different, once the values are pasted the destination cells becomes the colour of the origin column.
Dragging the paint format tool modifies the hidden rows so i cannot fix the problem that way

Thanks!!

Hi Petille

The copy process done in the macro is copying the value into the formula of the destination cell. I can't remember at this distance if putting the value into the value didn't work or if that's how the macro recorder did it, but that is all that should be updated it shouldn't be changing the destination column format at all. I have tried it changing the code to changing the destination cell value rather than it's formula and it still works the same.

the line

[COLOR=#333333]Range(dataDest).Offset(y, 0).Formula = c.Value [/COLOR]

changes to

Range(dataDest).Offset(y, 0).Value = c.Value

In my version I have included this line to set the background colour of the pasted data

Range(dataDest).Offset(y, 0).Interior.ColorIndex = 6

Where the number 6 is yellow, there is a list in the help somewhere that describes the colour options

I note if I copy from a column that is formatted as currency then the destination does pick that up but background colours do not get transferred, neither do borders.

Excel does sometimes guess at the formatting you want when adding data to a column.

Are you able to send a small sample of your data for me to see what's happening?

Ron

 

[Petille]

Good morning Ron and thank you for your reply. I attached a sample of the Binder I am working on.

Requête300 is one of 14 matrixes I created to be used in an iterative system where it is the source to create UAT testing forms and in turn is populated with the results from such tests. I am not a developer and although I can read VBA I cannot not write it on my own (I wish i could fit the learning time/curb in my week though!). Querying Google has provided me with solutions that i have adapted for my needs so that's how i have managed to survive in my position for the last 4 months .

In the sample, you will find two tabs, the first one being the matrix and the second one, T3x001c001.00, being the testing form for that specific matrix. Once the form was created, I filtered by colour in order to have only the header lines showing and, using that great macro you created, I filled these headers with the variants I needed to test, which are in column AC in the matrix.

I resolved my colour and font size problem by doing a find and replace on format; I was lucky that there was no other orange on the test form so i could have all the turned orange headers turned back to lime green.

For the test reference numbers at the end of the header lines, the macro did not do as well: (#ref? was the result).

This is because in my other matrixes, for each row I generate a single testing form with a macro that renames a template by using the concatenated Test ID column (AR).

Your copy to visible cells macro gets the formula rather than the value so it does not work on that column. I resolved my problem by copying the column and pasting as values only in an empty column which i used with your macro and that worked

http://sdrv.ms/152D2JB

 

[rondeondo]

Hi Petille

I don't understand where you're copying from and to. The macro does just copy the value of the source cell to the destination cell. If you are copying from one sheet to another the macro won't do that, it only works within the one worksheet.

The aim of the macro is to copy value into a filtered list of cells. If you're copying into a sheet that is not filtered you don't need to use the macro.

If you need to copy from one sheet to another you need to use excel copy and paste to get the source data into the destination worksheet. It may be possible to make it work across worksheets but I don't think it's necessary.

Ron