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

 

[Joe4]

I am unable to reproduce your situation.
Are you sure that you are not pasting this into a text file that already has a bunch of spaces in it?

Maybe we can find an alternative approach to what it is you are trying to do.
What are you ultimately trying to accomplish here?
Are you copying/pasting this value into a new text file or an existing one?

 

[ChrisMcIntyre]

Hi Joe4, thanks for responding.

What I want to be able to do is have the two "COPY" buttons in the screen grabs above, grad the text in the adjacent cell. Right now the codes does the job, I just cannot work out how to remove the extra spaces.

I have seen references to TRIM on various forums, but as an absolute novice, I am not sure how to build and integrate it into my code.

 

[Joe4]

Please answer all the questions I asked in my first reply.

 

[ChrisMcIntyre]

I am using the copy buttons in my testing to copy and then paste into a blank text file. Once a solution has been found to my questions the data will be pasted into a field in a database, but needs to exclde the extra spaces/gaps at the end of each value.

 

[Joe4]

I am using the copy buttons in my testing to copy and then paste into a blank text file. Once a solution has been found to my questions the data will be pasted into a field in a database, but needs to exclde the extra spaces/gaps at the end of each value.

I do not see anything wrong with your code. There isn't any way it should have any extra spaces at the end, as this part shouldn't really allow it:

TEXT(K1,"hhmmss")

There is nothing after this, so I don't see how it is possible.

I suspect the issue is more on the clipboard or pasting to NotePad side.

Perhaps set it equal to a variable and apply the Trim?
Dim myVal a String

myVal = Application.WorksheetFunction.Trim(Range("J2"))

and set your value in your database directly equal to this variable?

Out of curiosity, what kind of database is this?
Is it Access?
Access databases can link directly to Excel files, so there would be no need for a copy/paste.

 

[ChrisMcIntyre]

As mentioned, i am a total novice, so can you clarify or show me the entire solution you are proposing please, maybe show how the entire code looks?

I am not sure what "kind" of database it is, but it is not Access or any Microsoft application.

The function needs to be as designed please.

 

[Joe4]

Before we go running in circles chasing our tails, did you try copying it into your database, as you currently have it?
As I said, there is nothing that I can see that should produce extra spaces. So I think the way you are doing it in NotePad might be throwing a "red herring", and you may be trying to fix a problem that really does not exist.

Also note that although it should be totally unnecessary, you can add TRIM to your existing formula, i.e.

=TRIM(CONCATENATE(J1,TEXT(K1,"hhmmss")))

And there is no need to select a cell in VBA before copying it.
This:

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

can be simplified to this:

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

 

[ChrisMcIntyre]

Here are the text examples I am trying to copy from in my test file...

Here's the result of the copy paste into the Order Name field. I highlighted the field to show all the text/spaces.

Here's the result of the copy paste into the Plan Number field. I highlighted the field to show all the text/spaces.

I removed all characters and spaces from each field in the database.

I then included the TRIM text in the formula for both cells...

Plan Name: =TRIM(CONCATENATE(J1,TEXT(K1,"hhmmss")))
Order name: =TRIM(AONG!E2)

Re-tested the copy and paste and got the same results again.

This field requires 3 backspaces to remove the extra spaces...

This field requires just one...

 

[Joe4]

In your previous example, is that you pasting it into NotePad or in to the database?

I think I may possibly have an idea of what is going on.
In your original example where you are joining J1 and K1, enter this formula in ANY blank cell and tell me what it returns:

=ISNUMBER(K1)