Macro Help In Excel COPY PASTING [Closed]

Report
-
 Erwin VDB -
Hello,

I am trying to copy data from a excell spread sheet that is actually a set template i.e.

Name:John Doe Age:17

Job: Painter Favorite Food: Pizza

I think it would be easy to copy the fields since they are stationary and move to a different book or atleast copy onto clipboard then be able to paste like


First Name JOB FOOD Age Less Than Me
--------------------------------------------------------------------
John Doe Painter Pizza 6

3 replies

Will This Help ? (below...)

'Macro to copy the text in several cells on several worksheets to the clipboard for pasting.
Public Sub CopyIt()

'I assume you will loop through several worksheets in a workbook so I created a sheet object.
Dim Sheet As Excel.Worksheet

'I made a variable to hold the text from the cells.
Dim Txt As String

'I created a data object to use with the clipboard.
'You must have 'Microsoft Forms 2.0 Object Library' checked in "Tools" - "References"
'in order to use the 'DataObject' type.
Dim MyData As New DataObject

'Clear the text.
Txt = ""

'Loop through each of the sheets in the workbook...
'If you only want one worksheet then you don't need the loop or the sheet variable,
' instead you can use 'ActiveSheet' or name the sheet directly.
For Each Sheet In ActiveWorkbook.Sheets

'Add each cell in the sheet to the text variable using the cells function.
'I just put numbers in this one the numbers you have will depend on where the data is
'in your form. ( the vbtab tells it to move over by one column before the next data is added. )
Txt = Txt & Sheet.Cells(4, 5) & vbTab
Txt = Txt & Sheet.Cells(6, 5) & vbTab
Txt = Txt & Sheet.Cells(8, 5) & vbTab
Txt = Txt & Sheet.Cells(4, 7) & vbTab
Txt = Txt & Sheet.Cells(6, 7) & vbTab
Txt = Txt & Sheet.Cells(8, 7) & vbTab 'You could put the vbCrLf below here instead of vbTab.

'This tells it to add a return and line feed so the data from the next sheet
'will go to the next line instead of one big long line.
Txt = Txt & vbCrLf

'Finish the loop.
Next Sheet

'This uses the Data Object to put the collected text on the clipboard.
MyData.SetText Txt
MyData.PutInClipboard

'You are now ready to paste !

End Sub
17
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Hi:

I just read you response and was REALLY impressed that you made the effort and took the time to explain what your proposed solution was doing line by line. I have been programing for over 25 years in other environments than MicroSoft and have found their environment very difficult to transition into. Your's is the first proposed solution in years that I have seen wtih comments. Dang you must be on heck of a good programmer!!

I have a similar situation as the requestor of the issue to which you responded. My variation is:
- copy rows which have a specified data in a particular column to the end of a designated worksheet and then delete that row

Your assistance in this matter will be GREATLY appreciated!!!

One more thing, do you have any books or other resources you would recommend on how to more effectively transition into MS VB especially as it applies to Excel?

REALLY Appreciative of Your Work,
John
Hello,

I work with Excel 2010. I am having an error 91 Object variable or Block variable not set on the line Txt = Txt & Sheet.Cells(4,5) & vbTab.

I am not a professional programmer. Could you please help me to work around this error? Thank you
Posts
1
Registration date
Thursday May 27, 2010
Status
Member
Last seen
May 27, 2010

I am aware that Excel VBA is able to copy text files line by line.

However, is it possible to test that certain columns in the text file
has information and to store that data(1) in a variable on a sheet?

DATE TIME SUMMER TIME DCAT
100519 84003 NO 0

PSTNI 461 85 153 223

DATE TIME SUMMER TIME DCAT
100519 84503 NO 0

PSTNI 461 77 161 223

DATE TIME SUMME RTIME DCAT
100519 85003 NO 0
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
763
By storing data in a variable, I guess you mean to say add to the sheet cells

For that either you import the complete data and then remove what you need
or you open the file in the code and read line at a time and only use what you need to use. Generally speaking it is faster to import all then delete. but in some cases line by line is quicker and only thing that is possible.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!