VBA copy button issues

[ChrisMcIntyre]

Hi there!

I have a worksheet that needs to include two "copy" buttons that trigger macros to copy the text in the adjacent cells.

Issue #1
J2 has this formula =CONCATENATE(J1,TEXT(K1,"hhmmss")) that displays just nicely the text I need

but when I copy the cell I have a huge gap/space appear after the text, is there anyway to remove that so when I paste it's just the text?

Here's how it looks when I paste into Notepad, I've highlighted so you can see the entire pasted info

This is my super basic code so far...

Sub COPYPLANNUMBER()
Range("J2").Select
Selection.Copy
End Sub

Issue #2
H3 has this formula =AONG!E2 (just references some text on another sheet).

When my macro copies the cell I again have a huge gap/space appear after the text, is there anyway to remove that so when I paste it's just the text?

Here's how it looks when I paste into Notepad, I've highlighted so you can see the entire pasted info

The super basic code so far...

Sub COPYORDERNAME()
Range("H3").Select
Selection.Copy
End Sub

 

[ChrisMcIntyre]

In all tests I have pasted into Notepad and into the database fields.

J1 contains "UK-" (can change to other options) and K1 contains the date time that is then converted to just a time format.

Added the formula to a blank cell and got the word "TRUE" appear.

 

[NoSparks]

Just a thought,
Have you selected J2 and used 'Evaluate Formula' on the 'Formulas ribbon' to see if something shows up goofy in the calculation of the formula?

 

[ChrisMcIntyre]

Hi NoSparks!

Thanks for your help here, this is what I get...

 

[Joe4]

In all tests I have pasted into Notepad and into the database fields.

J1 contains "UK-" (can change to other options) and K1 contains the date time that is then converted to just a time format.

Added the formula to a blank cell and got the word "TRUE" appear.

Then I can see absolutely nothing that should be causing the extra spaces at the end.

How exactly are you pasting the value? Are you just using CTRL+V?

I wonder if you might need to use a different method of copy/pasting, like shown here:

How To Use VBA Code To Copy Text To The Clipboard

There may be instances where you create a macro that reads in text and sticks it in your computer's clipboard so you can manually paste it somewhere else. The most prominent way to do this, is to use a DataObject variable from the Forms library (language).

www.thespreadsheetguru.com

 

[ChrisMcIntyre]

No matter how I pastes, Ctrl+V, right click and paste as plain text or just paste, the extra spaces are coming across.

Ideally some code in the VBA to trim the end off the text string when copied would be the quickest solution if that's possible. That opposed to undoing all the other codes on other sheets that are getting this text to where it is now, and there's a lot.

 

[ChrisMcIntyre]

I wish I could share the doc but it has sensitive data and so I cannot, sorry.

Oh, and I tried looking at the "How To Use VBA Code To Copy Text To The Clipboard..." page, but unfortunaltey due to my limited knowledge this was an example of me getting a fish, rather than knowing how to fish, very sorry, I just could not work out how to apply any of that to my solution.

 

[Joe4]

Ideally some code in the VBA to trim the end off the text string when copied would be the quickest solution if that's possible. That opposed to undoing all the other codes on other sheets that are getting this text to where it is now, and there's a lot.

The issue is once it is "copied", I don't think you can apply "TRIM" to it, which is why I had made the suggestion of using TRIM in the formula on the sheet. I trust that you have tried that.
You mention "all the other codes". It very well could be that something is interfering with what you are trying to do.
Without seeing all that is going on, it makes it very hard for us to try to figure out what might be going on.

I wish I could share the doc but it has sensitive data and so I cannot, sorry.

Many people simply replace the "sensitive data" with dummy data, and then upload that.
If you do that, I would be willing to take a look at it and see if I can spot anything causing the issue.
Otherwise, I am out of ideas and really have nothing left to suggest.

 

[ChrisMcIntyre]

The trim or whatever the workable solution is, would occur at the point of the COPY macro running maybe.

The other codes I mentioned are all the ones that are getting text from mutiple locations and delivering it to this cell I need to copy. It could well be that extra spaces are coming down the pipe, but I cannot investigate all that code & functionality, would prefer a less ideal but cheap solution.

Sorry, the effort to remove all the sensitive data WAY outdoes the effort spent on a fix here, sorry. The document is huge, full of macros, formals, logos and text, all of which is too much to edit.

Appreciate the efforts and feedback so far, if anything comes to light that would help please let me know, even if it's a bit clunky, something is beter than nothing.

 

[ChrisMcIntyre]

Sorry, how about if one could add some additional code to paste the initial copy to another cell somwhere else, where the text could be trimmed and re-copied to the clipboard?

Could that work and be easy to design?

 

[Joe4]

Try something like this to put the value from J2 in cell Z2:

Range("Z2").Value = Trim(Range("J2").Value)

and try copy/pasting that.

Does that work?
If not, select cell Z2 and press the F2 key to bring up cell Z2 in edit mode.
Does it show the cursor right after the last number, or does it show spaces after the last number.