There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash desktop driver drivers error ethernet excel freeze gaming hard drive hardware hdmi internet laptop malware memory modem monitor motherboard netgear network printer problem ram registry repair router slow software sound toshiba trojan usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless xbox
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Solved: Excel Macro -> Use LEFT Function with Paste Special

Reply  
Thread Tools
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 09:58 AM #1
Question Solved: Excel Macro -> Use LEFT Function with Paste Special
Is there a way to use the LEFT Function with Paste Special?
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
30-Oct-2009, 10:06 AM #2
You can use the left function before using the equivelent of paste special, but does it need to be a paste operation?
Are you "pasting" to more than one cell?
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
30-Oct-2009, 10:10 AM #3
You want to write code to just copy the leftside of a cell? How a many characters?

Where is it to be pasted? - In place?
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 10:15 AM #4
I apologize for not giving better details.

Here is what I am trying to accomplish.

I need to copy a range of cells H5:H23. I only want to take the first 5 characters. I then need to paste the cells in R5:R23.
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
30-Oct-2009, 10:21 AM #5
try this

Code:
 
Sub Macro2()
For x = 5 To 23
Cells(x, "R") = Left(Cells(x, "H"), 5)
Next
End Sub
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
30-Oct-2009, 10:25 AM #6
turbodante, nice code and not a "paste" in sight
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 10:32 AM #7
Turbodante, very nice.

I have to perform this once more, but what if the cells where the values are going to be pasted are not the same?

For Example, the cells D5 D23 are to be copied and pasted to V25:V43.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
30-Oct-2009, 10:40 AM #8
Then you would use
Cells(x, "R") = Left(Cells(x+ 18, "H"), 5)
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
30-Oct-2009, 11:29 AM #9
Quote:
Originally Posted by OBP View Post
Then you would use
Cells(x, "R") = Left(Cells(x+ 18, "H"), 5)

perhaps OBP mean to say...

Code:
Sub Macro2()
For x = 5 To 23
Cells(x + 20, "V") = Left(Cells(x, "H"), 5)
Next
End Sub
..surely
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 14,665 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
30-Oct-2009, 11:53 AM #10
Yep, sorry about that, doing 2 things at once.
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 12:15 PM #11
I have one more scenerio. What if you want to paste this information on a different worksheet?

So, I want to copy data from H5:H23, only take the first five characters, and paste the valus in G32:G52

The reason for all the scenerios is I am still determining which way I plan to setup teh workbook.
__________________
Computerman29642
"Why so serious?....let's put a smile on that face" - The Joker
turbodante's Avatar
Senior Member with 744 posts.
 
Join Date: Dec 2008
Location: GMT UK
30-Oct-2009, 12:43 PM #12
If you want to paste to different sheets

Code:
Sheets("target").Cells(x + 27, "G") = Left(Sheets("Sheet1").Cells(x, "H"), 5)
In the above, I've called the sheet where it'll paste 'Target'.

[Though was OBP says, paste without pasting]

Last edited by turbodante; 30-Oct-2009 at 12:51 PM..
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 01:10 PM #13
Thanks Turbodante. You have helped me so much.
computerman29642's Avatar
Computer Specs
Senior Member with 2,794 posts.
 
Join Date: Dec 2007
Location: HERE OR THERE?!?!?!
Experience: Always Learning!
30-Oct-2009, 02:41 PM #14
Thank you OBP as well.
Zack Barresse's Avatar
Computer Specs
Distinguished Member with 5,030 posts.
 
Join Date: Jul 2004
Location: Oregon, United States
Experience: I'ma learnin'!
30-Oct-2009, 09:51 PM #15
With the suggestion of an array, it would take out the need to loop through cells, which is generally less efficient than looping through an array. The other option is to use a range of cells and apply a formula to it all at once, use that range to copy at once, delete (clean up) and move as desired. Just more options.
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 12:13 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.