Macro Help In Excel COPY PASTING

theloniousmonk - Sep 24, 2008 at 04:57 PM
 Erwin VDB - Jan 9, 2012 at 12:26 PM

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

'You are now ready to paste !

End Sub

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,

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
hopper10 Posts 1 Registration date Thursday May 27, 2010 Status Member Last seen May 27, 2010
May 27, 2010 at 09:02 PM
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?

100519 84003 NO 0

PSTNI 461 85 153 223

100519 84503 NO 0

PSTNI 461 77 161 223

100519 85003 NO 0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
May 28, 2010 at 04:07 AM
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.