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?

 

[RMInnovations]

Re: Paste to visible cells only (short macro)

I like quick code. You could also use sendkeys for this, but I wrote this for myself today and it works great for my needs:

Sub GoToVisibleCellsOnly()
'
' GoToVisibleCellsOnly Macro
' This macro is for pasting values to visible cells only. _
It is an automated equivalent of a user clicking _
GoTo, selecting Visible Cells Only, then Pasting.

' Assumptions: the user has selected and copied a value or values _
prior to executing this macro.
'
' Keyboard Shortcut: Ctrl+Shift+G
'
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Paste

End Sub

 

[Mrs Ramos]

I had the same problem and this is the solution that I use. It doesn't invovle writing macros, since I am still very very new to that and they tend to go haywire on me.

A Non-Macro Solution:
You can go to your excel options, click on customize, then from the drop down choose all commands, then scroll down until you see Select Visible Cells. Select add to customize wuick access toolbar.

To use that button: first copy the cell that you want, then select the cells that you want to copy into, click the select visible cells button, then paste as normal.

Hope this helps!

 

[DOF2001]

hi,

I was able to modify some code found on the internet, it allows me to e-mail and excell sheet using outlook, the context of the sheet is pasted as the bosy of the e-mail.


Just some questions:

1.- the sheet i want to send will have some hiden rows based on user, but the code e-mails hte all rows, any way to only send visible cells

2.- Any way to e-mail the sheet to specific person based on the type of request, if the request is "A" will go to perso "X" other wise it willgo to person "Y" Cell D4 containes the value of "A"

Sub Mail_Sheet_Outlook_Body()
' Don't forget to copy the function RangetoHTML in the module.
' Working in Office 2000-2010
    Dim rng As Range
    Dim OutApp As Object
    Dim OutMail As Object
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
 
    Set rng = Nothing
    'UsedRange = Selection.SpecialCells(xlCellTypeVisible).Select
    Set rng = ActiveSheet.UsedRange
    'You can also use a sheet name
    'Set rng = Sheets("YourSheet").UsedRange

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
 
    On Error Resume Next
    With OutMail
        .To = "diego.flores@mmc.com"
        '.CC = ""
        .Subject = "This is the Subject line"
        .HTMLBody = " To the Sender: " & vbNewLine & _
         "If requesting a report for more than one company please" _
        & "attached file with other companies' information" _
        & RangetoHTML(rng)
        .Display  'or use .Display
    End With
    On Error GoTo 0
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
 
    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub
Function RangetoHTML(rng As Range)
' Changed by Ron de Bruin 28-Oct-2006
' Working in Office 2000-2010
    Dim fso As Object
    Dim ts As Object
    Dim TempFile As String
    Dim TempWB As Workbook
 
    TempFile = Environ$("temp") & "/" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
 
    'Copy the range and create a new workbook to past the data in
    rng.Copy
    Set TempWB = Workbooks.Add(1)
    With TempWB.Sheets(1)
        .Cells(1).PasteSpecial Paste:=8
        .Cells(1).PasteSpecial xlPasteValues, , False, False
        .Cells(1).PasteSpecial xlPasteFormats, , False, False
        .Cells(1).Select
        Application.CutCopyMode = False
        On Error Resume Next
        .DrawingObjects.Visible = True
        .DrawingObjects.Delete
        On Error GoTo 0
    End With
 
    'Publish the sheet to a htm file
    With TempWB.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=TempFile, _
         Sheet:=TempWB.Sheets(1).Name, _
         Source:=TempWB.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
 
    'Read all data from the htm file into RangetoHTML
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
    RangetoHTML = ts.ReadAll
    ts.Close
    RangetoHTML = Replace(RangetoHTML, "align=center x:publishsource=", _
                          "align=left x:publishsource=")
 
    'Close TempWB
    TempWB.Close savechanges:=False
 
    'Delete the htm file we used in this function
    Kill TempFile
 
    Set ts = Nothing
    Set fso = Nothing
    Set TempWB = Nothing
End Function

 

[curfubbled]

I'm having slight different issue. When I copy from one workbook, first selecting the Select Visible Cells option, then copying. it is still pasting the hidden cells. If I do this on another computer it works fine, so I don't know if it is a setting in my excel or some other weird issue. Any suggestions? And thanks!

 

[rockyw]

I had a like problem recently. I made a macro that deleted the blank rows, selected the range and copied, selected the area to paste into, then pasted using paste special, values only. That is if the area is always the same range.

 

[gthull644]

Okay, I don't think the first way will work. What about this. With the filtered list, select the top cell in col Bsay b2, enter the formula =A2. Copy cell B2, select the rest of colb down to the bottom of your data, do the Alt; bit paste.

Then if you dont want formulas do edit, paste special, values.

A bit long winded maybe

I think other posts are getting away from the original question which was how to copy and paste to different parts of a filtered list. GorD's soultion above works for me. The phrase "do the Alt; bit paste", just means select visible cells by pressing Alt and ; and then paste.

 

[rondeondo]

I think other posts are getting away from the original question which was how to copy and paste to different parts of a filtered list. GorD's soultion above works for me. The phrase "do the Alt; bit paste", just means select visible cells by pressing Alt and ; and then paste.

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

 

[Maxim_G]

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

Hey there,

I'm very new to Macros, but when I try the above method either nothing happens, or, if I select Visible Cells Only when creating the global variable source I am met with an Run-time Error 1004, Method 'Range' of object '_Global' failed. The debugger takes me to the line:
Range(dataSrc).SpecialCells(xlCellTypeVisible).Select

and when I hover over "dataSrc" it shows as empty.

Help please!

 

[rondeondo]

Hi Maxim

I don't think I explained the usage so well.

Fist you highlight the cells to be copied and run the first macro that stores the address range of the cells in dataSrc

Then you select the cell at the top of the column to paste into and run the second macro. This then steps through each cell in the DataSrc range and does copy paste values one cell at as time.

I've also created a couple of toolbar buttons to run these macros

Highlight cells to copy
run FilteredCopyValues
select cell to start copying into
run FilteredPasteValues


Ron

 

[Maxim_G]

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