Copy visible cells as text (without formatting)

[kev71986]

Hi Guys

I've encountered a problem which seemed fairly easy to resolve and has now resulted in me tearing my hair out!

I have a worksheet which hides and shows cells based on user input, fairly simple, lots of code, but works well. At the bottom of the worksheet there is a button to "Copy Notes", which will allow the user to paste the aforementioned selections onto their systems.

I need this macro to only copy visible cells and their values, excluding the formatting. From playing around with the macro I decided to copy the visible cells, then paste the values from these visible cells elsewhere on the sheet. I would then require a code to copy cells which only contain data from the now pasted cells (unless anyone can think of anything easier?).

The code I currently have is;

Private Sub Commandbutton1_click()

Set rng = Sheets("Sheet1").Range("b2:q170")
rng.SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet1").Range("b190").PasteSpecial xlPasteValues

'Notify User
MsgBox "Text notes have now been copied to your clipboard", vbInformation

End Sub

So from using my code I would then need a code which looks at the range B190:Q359 and only copies cells which contain data.

I think I've went the long way round for a shortcut, but would really appreciate any suggestions.

Thanks

 

[Wookiee213]

Could you re write it and just loop through the range and assign the value to the target cell if it wasn't empty?