p85ki
08-19-2018, 11:49 AM
Hi all,
I am new here, I do alot in Excel and have taken my hand in VBA (slowly) and learning. Currently I am stuck and have no idea how to get this to work in the way I want. I am reading but there's soo much information I really am getting lost with it. :(
Please see below in what
How it's currently working
• I have some text within a few cells.
• This text is displayed once a user has selected a few filters.
• I have a formula which reads how many cells will provide me text so it's a little dynamic. The rule of thumb is all text will always be within cells A3-A11.
• The text will display in A3,A5,A7,A9,A11 BUT there's never any text in A2,A4,A6,A8 and A10.
• The reason behind this is so that when I press the "Copy Text" button I have created, it copies those ranges of cells which will include the spacing between them.
• When I press the "Copy Text" button this copies and pastes into a Notepad.
• This allows the text to display in the Notepad as new sentences and not just a line under the other (or quotation marks at the start/end of the text).
This use to be a great solution, but now I really need to improve this but not sure how. :(
The issue with the above
• The first cell is always formatted and 1 of the other cell is also formatted. How I do it at the moment doesn't copy the formatting as it is, which means I have to always add this in.
• Skipping a cell is only trying to ensure I can copy this into a notepad, ideally I just want to put this one under the other.
• When this copies into the Notepad, it also has large white spaces that shouldn't be there.
• The reason I paste this into a Notepad is to remove the "Table" cells, otherwise if this was paste directly in Word 2013 (then the table cells will show invisible).
• The way this works is no longer practical and need a better solution.
• The information is not being copied from one spreadsheet to another, this is being copied from a spreadsheet to an online form (which the form url is dynamic so unable to link this to that).
What I need help with
• To copy each one of those cells and keep the formatting/font to whatever is selected as it is (i.e. where I have it in bold that is remains in bold).
• Where I have the information in a new cell, it creates 2 line breaks and doesn't copy it as a table (as if it's a cell).
• That it removes any spacing at the beginning and end of the sentence.
• This copies into a clipboard and confirmation message to say text has been copied to clipboard.
• Naturally if it's copied, then this should paste the information onto the online documentation with the formatting as it is.
Here is the standard code I am currently using:
Private Sub CopyProblemText_Click()With Application
' Selects Range you want to do something with
Range("$A$3:$A$11").Select
' Copies the Range you selected
Selection.Copy
Selection.PasteSpecial xlPasteFormats
' Command to open Notepad
'Shell "Notepad.exe", 3
' Uses the CTRL+V to paste into Notepad
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With
End Sub
I really have gone round the houses with this but cannot find a solution.
Can anyone provide any assistance, either the code required or help with understanding this (is this really soo complex to do).
Thank you in advanced.
I am new here, I do alot in Excel and have taken my hand in VBA (slowly) and learning. Currently I am stuck and have no idea how to get this to work in the way I want. I am reading but there's soo much information I really am getting lost with it. :(
Please see below in what
How it's currently working
• I have some text within a few cells.
• This text is displayed once a user has selected a few filters.
• I have a formula which reads how many cells will provide me text so it's a little dynamic. The rule of thumb is all text will always be within cells A3-A11.
• The text will display in A3,A5,A7,A9,A11 BUT there's never any text in A2,A4,A6,A8 and A10.
• The reason behind this is so that when I press the "Copy Text" button I have created, it copies those ranges of cells which will include the spacing between them.
• When I press the "Copy Text" button this copies and pastes into a Notepad.
• This allows the text to display in the Notepad as new sentences and not just a line under the other (or quotation marks at the start/end of the text).
This use to be a great solution, but now I really need to improve this but not sure how. :(
The issue with the above
• The first cell is always formatted and 1 of the other cell is also formatted. How I do it at the moment doesn't copy the formatting as it is, which means I have to always add this in.
• Skipping a cell is only trying to ensure I can copy this into a notepad, ideally I just want to put this one under the other.
• When this copies into the Notepad, it also has large white spaces that shouldn't be there.
• The reason I paste this into a Notepad is to remove the "Table" cells, otherwise if this was paste directly in Word 2013 (then the table cells will show invisible).
• The way this works is no longer practical and need a better solution.
• The information is not being copied from one spreadsheet to another, this is being copied from a spreadsheet to an online form (which the form url is dynamic so unable to link this to that).
What I need help with
• To copy each one of those cells and keep the formatting/font to whatever is selected as it is (i.e. where I have it in bold that is remains in bold).
• Where I have the information in a new cell, it creates 2 line breaks and doesn't copy it as a table (as if it's a cell).
• That it removes any spacing at the beginning and end of the sentence.
• This copies into a clipboard and confirmation message to say text has been copied to clipboard.
• Naturally if it's copied, then this should paste the information onto the online documentation with the formatting as it is.
Here is the standard code I am currently using:
Private Sub CopyProblemText_Click()With Application
' Selects Range you want to do something with
Range("$A$3:$A$11").Select
' Copies the Range you selected
Selection.Copy
Selection.PasteSpecial xlPasteFormats
' Command to open Notepad
'Shell "Notepad.exe", 3
' Uses the CTRL+V to paste into Notepad
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With
End Sub
I really have gone round the houses with this but cannot find a solution.
Can anyone provide any assistance, either the code required or help with understanding this (is this really soo complex to do).
Thank you in advanced.