Copy and Paste

Microsoft Office 2007 home and student
February 26, 2010 at 01:03:20
Specs: Windows 7
Can anyone help me?
I have a macro that copies and pastes one piece of data at a time
Sub PasteText()
ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
DisplayAsIcon:=False
End Sub
But I wish to use a macro that will copy multiple pieces of data (can vary from 2 to 20 pieces) then paste on Microsoft office Excel starting from different locations down columb G with 8 rows separating each piece of data
Thanks.

See More: Copy and Paste

Report •


#1
February 26, 2010 at 04:20:46
Hi,

Some questions:
1. How will the macro 'know' how many copy and paste operations to undertake?
2. Where is the macro copying from - is it from another worksheet in the same workbook, is it from a Word document etc. - you have only shown the code you are using for the paste part of the operation - nothing about the copy.
3. Is the data (1 to 20 pieces) single pieces of data that goes into one cell. If copying and pasting 3 items for example you will end up with data in cells G1, G10 and G19 only.

Regards


Report •

#2
February 28, 2010 at 05:05:07
1/The macro would have to me programmed so that it would process the data stored on the clipboard
2/The macro is copying from a web page and must be converted to paste special Text (without any formatting for it to show the data as copied and not all in one line.
3/The data when retrieved is entered into cell G and stretches 10 cells to cell P and can vary in depth.
4/All individual pieces of data fit underneath one another but not necessary touching eg 1 piece fitted in cell 9G next in cell 17G,next in cell 25G (separated by a 8 row buffer to allow for different widths of the data.
5/If pasting 3 items from the clipboard the first would start in 9G,next start in17G and then start in 25G until clipboard is empty then new data would start 8 rows lower than the last data processed. e.g. 33G
6/My worksheet at present caters for data up to row 4000
Thanks Kuzzy

Report •

#3
February 28, 2010 at 06:35:13
Just to make things easier on all of us, it would help if you used "standard" Excel terminology.

re: entered into cell G and stretches 10 cells to cell P

That should written as into Column G and stretches 10 columns to Column P

re: fitted in cell 9G, next in cell 17G, next in cell 25G

Excel references are written as G9, G17, G25, etc. (Column first, row second).

Ranges are referred to as G9:G17, G25:J56, etc.

re: a 8 row buffer to allow for different widths of the data.

Width is typically used for data that span across columns and height is used for data that spans across rows.

Thanks!

As to your request, I think we need a little more information. I'm not sure that we call pull individual pieces of data from the clipboard and place them where we want. The data will probably need to be pasted into the sheet and then rearranged.

What does the data on the web page look like? Any chance you could post a link or a link to a screen shot?

Have you considered saving the data in a csv file and then manipulating that file to get it laid out as you want?


Report •

Related Solutions

#4
February 28, 2010 at 09:58:57
If I copy and paste the data below is shown.(All in 1 row)
RACE TYPE STARTS WINS 2NDS 3RDS WINNINGS EARNINGS BEST TS BEST RPR OR† Lifetime Hurdle 5 0 0 1 £0 £644 93 110 110 Lifetime All 5 0 0 1 £0 £644 - - -
The data I require is pasted as Paste special (Text)
RACE TYPE STARTS WINS 2NDS 3RDS WINNINGS EARNINGS BEST TS BEST RPR OR†
Lifetime Hurdle 5 0 0 1 £0 £644 93 110 110
Lifetime All 5 0 0 1 £0 £644 - - -
Is this any help?

Report •

#5
February 28, 2010 at 10:05:44
I'll try again:

What does the data on the web page look like? Any chance you could post a link or a link to a screen shot?

Have you considered saving the data in a csv file and then manipulating that file to get it laid out as you want?


Report •

#6
February 28, 2010 at 11:05:47
http://www.racingpost.com/horses/ho...

This is a link the data required is from RACE TYPE to the line Lifetime All ends 2 rows under 92


Report •

#7
February 28, 2010 at 11:56:56
Actually seeing the data helps me a lot.

Now that we are looking at the same data, explain to me where the 8 row "buffer" goes. Under each line or under this group of lines?

I see that some horses have 3 lines of data (IRE) while others only have 2. Does that matter?

Are you copying and pasteing the column titles - 'RACE TYPE", "STARTS", etc - every time?

This is just a curiosity question:

Sometimes I see a long list of horses in the Drop Down and sometimes the list is much shorter. What's up with that?


Report •

#8
February 28, 2010 at 12:59:49
http://www.racingpost.com/horses/ho...
this link shows a larger amount of data the biggest is 8 rows in total.Yes all the data is copied and paste and the other part of my spreadsheet converts this info into different categories so all different parts of data must be under each other in the same column 8 rows separating each piece of data
.The drop down list is dependant on how many horses are running in the race.
The 8 row buffer is to allow for the horse with the most data.
The 8 rows have already been incorporated into my spreadsheet to allow for each piece of data to start in column G separated by 8 rows

Report •

#9
February 28, 2010 at 15:40:13
I'm still slightly confused by what you are calling a piece of data.

Which result are you looking for:

RACE TYPE  	 STARTS    etc.
       7 Blank Rows
Lifetime Hurdle    6	
       7 Blank Rows
Lifetime Chase 	   3
       7 blank rows
Lifetime All 	   9
       7 Blank Rows
RACE TYPE  	 STARTS    etc.

or

RACE TYPE  	 STARTS    etc.
Lifetime Hurdle    6	
Lifetime Chase 	   3
Lifetime All 	   9
       7 Blank Rows
RACE TYPE  	 STARTS    etc.


Report •

#10
February 28, 2010 at 19:45:52
Hi,

I looked at the web pages you linked to.

The following code will paste the information you copied on the web page to the active worksheet at 8 row intervals. If the data copied is less than 8 rows, there will be empty rows following it. The next selection copied will be pasted at the start of the next eighth row, starting at column G

Here is the code

Option Explicit

Sub WebToCells()
Dim rngEnd As Range
Dim doCopy As New DataObject
Dim varArry As Variant
Dim intEightRow As Integer
Dim n As Integer

On Error GoTo ErrHnd

'get end of column G
Set rngEnd = ActiveSheet.Range("G" & CStr(Application.Rows.Count)) _
            .End(xlUp).Offset(1, 0)
If rngEnd.Row = 2 Then
    Set rngEnd = ActiveSheet.Range("G1")
    Else
    'needs to be at 8 row intervals
    intEightRow = (Int(rngEnd.Row / 8) + 1) * 8
    Set rngEnd = ActiveSheet.Range("G" & CStr(intEightRow))
End If

'get copy of clipboard
doCopy.GetFromClipboard

'split clipboard text at new line characters
varArry = Split(doCopy.GetText, Chr(10))
'put substrings into sequential rows
For n = 0 To UBound(varArry)
    rngEnd.Offset(n, 0).Value = varArry(n)
    'and parse column G into succesive columns
    rngEnd.Offset(n, 0).TextToColumns Destination:=rngEnd.Offset(n, 0), _
                DataType:=xlDelimited, _
                TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
                Tab:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
                Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
                Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
Next n

Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub

Note that for this code to work you must include a reference to Microsoft Forms 2.0 Object Library
(Tools - References and check the check box alongside Microsoft Forms 2.0 Object Library)

I have only tested this code on a limited number of pieces of text copied from the web pages you posted, so it may require changes to make it work exactly as you require.

Hope this helps

Regards


Report •

#11
March 1, 2010 at 01:54:47
I need to have all individual horse data to start at G9
G9=RACE TYPE
H9=STARTS
I9=WINS
J9=2NDS
K9=3RDS
L9=WININGS
M9=EARNINGS
N9=BEST TS
O9=BEST RPR
P9=OR
then after the lines of Data the next horses to start in G17 with its Data followed by G25
The data must read as a block not in a straight line because this data is read and sorted by formulas on my work sheet.
I have copied your macro from Option Explicit
to Err. Clear
End Sub
and when I press Paste All from clip board I have lines of data not blocks,If I press Paste Special Text I have just the last stored data in the correct Block form.
Being just a novice at programming Macro's etc I haven't a clue how to include a reference to Microsoft Forms 2.0 Object Library
(Tools - References and check the check box alongside Microsoft Forms 2.0 Object Library)
The sheet being used is Microsoft Office Excel 2007
Thanks for all your correspondence
Kuzzy
Sorry to be a pain.

Report •

#12
March 1, 2010 at 04:39:12
Hi,

Being just a novice at programming Macro's etc I haven't a clue how to include a reference to Microsoft Forms 2.0 Object Library
(Tools - References and check the check box alongside Microsoft Forms 2.0 Object Library)

When you are in the visual basic window go to the Menu at the top and click on Tools. From the drop-down list click References...

This opens a dialog box and one of the items in the box is 'Microsoft Forms 2.0 Object Library'.

Each item has a check box alongside it - click the box to check the required Object Library. Note that there are several items with similar names so make sure you select the correct one.

There will be some other items already checked - leave them checked.

The code I provided puts 'RACE TYPE' in column G and so on with OR in column P.

I note that successive uses of this routine start each block 8 rows down at 8, 16, 24 etc instead of 9, 17, 25. I will make the required adjustment.

I note that in your post you say and when I press Paste All from clip board I have lines of data not blocks,If I press Paste Special Text I have just the last stored data in the correct Block form.

I don't understand what you mean by this. The macro that I provided does the pasting. You go to the web site, highlight the required data, click Copy, then go back to Excel, select the worksheet for the results, and then run the macro and the copied data is pasted into the cells as text in the next group of 8 rows in columns G to P.

Regards


Report •

#13
March 1, 2010 at 08:57:17
Hi,

Here is the code, modified to start at row 9 for the first Copy/Paste then at 8 row intervals - 17, 25 etc.

Option Explicit

Sub WebToCells()
Dim rngEnd As Range
Dim doCopy As New DataObject
Dim varArry As Variant
Dim intEightRow As Integer
Dim n As Integer

On Error GoTo ErrHnd

'get end of column G
Set rngEnd = ActiveSheet.Range("G" & CStr(Application.Rows.Count)) _
            .End(xlUp).Offset(1, 0)
If rngEnd.Row < 9 Then
    Set rngEnd = ActiveSheet.Range("G9")
    Else
    'needs to be at 8 row intervals 9, 17 etc.
    intEightRow = ((Int(rngEnd.Row / 8) + 1) * 8) + 1
    Set rngEnd = ActiveSheet.Range("G" & CStr(intEightRow))
End If

'get copy of clipboard
doCopy.GetFromClipboard

'split clipboard text at new line characters
varArry = Split(doCopy.GetText, Chr(10))
'put substrings into sequential rows
For n = 0 To UBound(varArry)
    rngEnd.Offset(n, 0).Value = varArry(n)
    'and parse column G into succesive columns
    rngEnd.Offset(n, 0).TextToColumns Destination:=rngEnd.Offset(n, 0), _
                DataType:=xlDelimited, _
                TextQualifier:=xlNone, ConsecutiveDelimiter:=False, _
                Tab:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
                Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
                Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1))
Next n

Exit Sub
'error handler
ErrHnd:
Err.Clear
End Sub


Regards


Report •

#14
March 1, 2010 at 09:43:45
Thanks Again
I,ve copied your latest version and when I drop down Visual Basic 4 iteams are ticked
1/Visual Basic for applications
2/Microsoft Excel 12.0 Objective Libary
3/OLE Automation
4/Microsoft Office 12.0 Object Libary
1 have scanned all the other avaliable ref and cann't find Microsoft Forms 2.0 Object Library'.
starts with Accessibility CplAdmin 1.0 Type Libary to XPS_SHL_DLL 1.0 Type Libary.
When I try to run your Macro
Have a Compile error
User-defined type not defined
doCopy As New DataObject is highlighted in Blue
When I press OK
Sub WebToCells () is highlighted in yellow

Report •

#15
March 1, 2010 at 10:02:36
Hi,

The error you are getting is specific to the missing 'Microsoft Forms 2.0 Object Library'.

First use your search function in Windows 7 to find the file FM20.dll
It may be in a hidden folder, so make sure your search looks in hidden or protected locations.
Take a note of its location.

Back in the VB Window, in the References list, use Browse to browse to the FM20.dll file and select it.
It will now show in the list.

Regards


Report •

#16
March 1, 2010 at 10:45:13
Managed to do that Microsoft Forms 2.0 Object Libary is ticked but if I copy 2 pieces of data only the last data copied loads into G9 and no data in G17 when I run the Macro?

Report •

#17
March 1, 2010 at 10:52:56
Hi,

When you say you copied two pieces of data, did you run the macro after each Copy, or did you copy twice and then run the Macro?

Regards


Report •

#18
March 1, 2010 at 10:55:41
Copied twice then ran the Macro

Report •

#19
March 1, 2010 at 11:42:47
Hi,

AFAIK Windows only retains one 'Copy'. When you did the second copy it erased the first copy.

So, unfortunately the problem is with Windows and an Excel macro can't overcome that.

I would be happy to hear otherwise!

To make using the macro easier you could run it from a button on your worksheet:

From the Ribbon select Developer (If Developer is not visible, go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box that opens as soon as you have drawn the button, select the macro from the list. Click OK.

Edit the button name - (before clicking on any anything else on the worksheet), or later, right-click the button and select Edit Text.

Click on any cell in the worksheet and the button is now active.

Copy from the website, use Alt+Tab to flip back to Excel, click the button to Paste the new data, Alt-Tab again to go back to the Web page and so on (I don't have Windows 7, so I can't be sure about Alt+Tab as a means of quickly moving back and forth between two windows).

Regards


Report •

#20
March 1, 2010 at 12:07:02
Thanks for trying but if I can't use the clipboard which can copy up to 20 items with a macro because it just lists data underneath each other in a line and not in a block then my original micro
ActiveSheet.PasteSpecial Format:="Text", Link:=False, _
DisplayAsIcon:=False
End Sub with short cut key ctrl a is my only option.
Thanks again
Kuzzy

Report •


Ask Question