Microsoft Excel - Paste to visible cell macro not pasting :(

Hi,

I found this 2008 post http://www.excel-answers.com/microsoft/Excel-Programming/31767690/paste-to-visible-cells-macro.aspx.

It is exactly what I need.  However, I don't seem to be able to have it run. 

I have 2 workbooks. I want to select a bunch of cells in workbookA (no hidden cells) and paste them into workbookB (filtered with hidden cells).

I tried to paste only in visible cells using the find and select / go to special / Visible cells only. It didn't work.

While searching on the net, it says that the only way to achieve it was to use a macro.

I found this macro (see link above) however, when I run it, the only thing that happen is a pop up box asking me to Select the first cell in the paste range.

After selecting the cell, nothing happen.


Can you help.

Thanks

Christine

Hello Christine,
I have tried it and it seems to work just fine with hidden rows.  However you do use the word 'filtered' which perhaps means something different to 'hidden'.  Perhaps that is the cause of your problem though I did try it with a simple filter in place and that too worked.  Are you sure that you have not modified the code in any way?  Perhaps you could post what you have used.
Regards
Harry

Hi Harry,

I haven't modified the code. However maybe I didn't paste it at the right place in my workbook? It's the first time I try to use a macro in excel. Below is the code I inserted in Module1 in my VBA interface. And when I go to to my macros/view macros, I can see CopyToVisibleOnly1. This is what I picked and clicked run. Before selecting it, I had selected my cells in my other workbook.

Option Explicit

Public StartWB As Workbook
Public StartWS As Worksheet
Public CopyRng As String

Public Sub CopyToVisibleOnly1()
'Start with cell selected that you want to copy.
Set StartWB = ActiveWorkbook
Set StartWS = ActiveSheet
CopyRng = Selection.Address
'Call CopyToVisibleOnly2 after a five-second delay.
Application.OnTime Now() + TimeValue("0:00:04"), "CopyToVisibleOnly2"
End Sub

Private Sub CopyToVisibleOnly2()
'Declare local variables.
Dim EndWB As Workbook, EndWS As Worksheet
Dim Target As Range, CurrCell As Range
Dim x As Long, FromCnt As Long
On Error GoTo CTVOerr
'Select the range where it should be pasted.
Set Target = Application.InputBox _
(Prompt:="Select the first cell in the Paste range", Type:=8)
Set EndWB = ActiveWorkbook
Set EndWS = ActiveSheet
Set CurrCell = Target.Cells(1, 1)
Application.ScreenUpdating = False
'Copy the cells from the original workbook, one at a time.
StartWB.Activate
StartWS.Activate
For x = 1 To Range(CopyRng).Count
StartWB.Activate
StartWS.Activate
Range(CopyRng).Cells(x, 1).Copy
'Return to the target workbook.
EndWB.Activate
EndWS.Activate
CurrCell.Activate
'Only cells in visible rows in the selected
'range are pasted.
Do While (CurrCell.EntireRow.Hidden = True) Or _
(CurrCell.EntireColumn.Hidden = True)
Set CurrCell = CurrCell.Offset(1, 0)
Loop
CurrCell.Select
ActiveSheet.Paste
Set CurrCell = CurrCell.Offset(1, 0)
Next x
Cleanup:
'Free the object variables.
Set Target = Nothing
Set CurrCell = Nothing
Set StartWB = Nothing
Set StartWS = Nothing
Set EndWB = Nothing
Set EndWS = Nothing
Application.ScreenUpdating = True
Exit Sub
CTVOerr:
MsgBox Err.Description
GoTo Cleanup
End Sub

Cheers, hope you can find my mistake.

Hi Christine,
Perhaps the macro needs to be in the workbook that you are pasting from.  You select the cells that you want to paste. Activate the macro.  You then have five seconds to change to the destination workbook and the dialog box will open. Then you select the cell where you want your paste to start and then click OK on the dialog box.
Hope this is clear and works for you.
Harry

Hi Harry,

it was a good thought but didn't happen. When I put the macro in the workbook from where I copy the cells, the pop up box doesn't get populated when I pick my pasting cell in my destination workbook.

If you think of anything else please don't hesitate to share.

Thanks

Hmmm,
Sounds a bit strange.  You do have to click on the cell when the Input Box is open, it doesn't populate itself.
If that is not the problem, perhaps you can post the file (suitable sanitised) to look at.
Regards
Harry

Hi,

Here are my 2 workbooks. I trimmed a few columns but you should get the gist of it. I want to copy data from Hole_Checks_test.xlsx (column D to I) which have no hidden rows to Hole_linked_test.xlsm (column c to H). This file has many hidden rows from applying a filter (Project = BY and ranking = 1).

I want to paste the values from the first file in the visible cells of second file. The macro at the moment sits in a module in the receiving file.

HOLE_CHECK.zip

Hope you can figure out :)

Cheers

Hello Christine,
I transferred the code to the copyfrom file and deleted it from the copyto file.  The process works OK except that it only copies a single column.  It will need some mods to be able to do the whole range I think.  I will look at it further.
Harry

Oh,

I didn't try only one column at a time. I'll try it now. As for where the code is stored I tried to put it in the copy from before and like I said, when I selected the cell to paste into, the cell id with $$ didn't show in the input box.

Anyway, I will try it out right away, thanks for your hard work.

Christine

Hi Harry,

I don't know what is different on my end but I can't pick a cell in the workbook I want to paste into if the macro is sitting in the workbook where I copy the cells from.

I don't understand how it is working for you but not for me. I am using windows 2010.

I was using 2 different instances of excel so I thought it might have been the problem. I opened my 2 workbooks in the same instance, put the code in my copy from file and removed it from my copy to file. It looked promising but them when I selected the first cell of the paste range I got the error "Activate method of Range class failed".

I'm at a loss here. Even if you figure out how to paste many columns, I don't seem to be able to reproduce your process at my end.

Regards

Hello Christine
Here is my version of the copyfrom file.  I have modified the macro to do the full selected range.
HOLE_CHECKS_TEST.zip
It now works fast but though I havent checked row by row, there does seem to be some sort of a breakdown after the row containing XX015.  Whether this is due to differences between the two files or not I can't say at the moment.
I too am using 2010 so that shouldn't be a problem.
Just to be clear and I am sure that you are doing it properly.  You are in this file with both open in the same instance of Excel.  Select the range that you wish to transfer (I have assumed that this is all data and no formulae). Activate the macro.  I have increased the wait time to 10 secs just to make sure that you have plenty to navigate to the other file.  When the Input box opens type the cell address or click on the top left hand cell of the range you want to copy to and Click OK.  After a few seconds the transferred data should appear.  Also, to be sure that there is no macro in the other workbook, when you open the macro dialog box, only one instance of the macro name (with no workbook title in front of it should appear).
I have eliminated all of the Activate needs in the modifications that I have made.
Hopefully this will be a goer.
Harry

Hello again Christine,
The SiteID data on the two sheets diverge after Row 158 and that will be why the data does not align in the bottom half of the sheet.
Harry

Hi Christine,
Could you also insert this snippet.
If Target Is Nothing Then
    MsgBox ("You pressed Cancel")
    Exit Sub
End If
It should go immediately after
On Error GoTo 0
Application.DisplayAlerts = True
It is just intended to give a warning that Cancel has been pressed before leaving the macro.
Regards
Harry

Hi Christine,
Could you also insert this snippet.
If Target Is Nothing Then
    MsgBox ("You pressed Cancel")
    Exit Sub
End If
It should go immediately after
On Error GoTo 0
Application.DisplayAlerts = True
It is just intended to give a warning that Cancel has been pressed before leaving the macro.
Regards
Harry
Sorry, you'd probably see this anyway.  Senior moment :)

Harry,

Thank you so much. It works!

I transferred everything and will look at your code later tonight.

Thanks again.

Christine