Related:
- Macro Help In Excel COPY PASTING
- Spell number in excel without macro - Guide
- Macros in excel download - Download - Spreadsheets
- How to take screenshot in excel - Guide
- Dd-mm-yyyy format in excel - Guide
- Excel free download - Download - Spreadsheets
3 responses
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
'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
hopper10
Posts
1
Registration date
Thursday May 27, 2010
Status
Member
Last seen
May 27, 2010
May 27, 2010 at 09:02 PM
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?
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 28, 2010 at 04:07 AM
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.
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.
Feb 19, 2010 at 09:46 AM
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
Jan 9, 2012 at 12:26 PM
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