Hi.
I recorded a macro for the data entry group I work with that will save them
lots of time. The only step that doesn't work is the last one, which copies
data back from the clipboard into the current worksheet.

The goal was to split the first column into three, (it contains a number
with three nodes delimited by "-"), eliminate duplicate rows based on the
first column only, and renumber the shortened list.

Here's what I did:

Insert two columns to the right of column A.
Select column A.
Data - Text to Columns - Delimited by "-"
Delete columns B and C
Select column A
Data - Filter - Advanced Filter - Filter in place, Unique records only
(duplicate rows are now hidden)
Click on the cell in the upper left corner to select all data
Copy to clipboard
Data - Filter - Show all
Delete
Paste from Clipboard


The error is: run-time error '1004', Paste Method of Worksheet Class failed.

I'm not well versed in vb but I read everything I could find in the help on
paste method, tried adding a parameter or two, to no avail. Any help would
be greatly appreciated. Worst case, the macro leaves them with a blank
worksheet and the data on the clipboard ready to paste in.

Copied the macro in below in case that helps. We're using Excel 2003.

P.S. Is it possible to email a macro so I don't have to record it on
everyone's machine?

Thank you!!


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/25/2010 by Me
'
' Keyboard Shortcut: Ctrl+g
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Range("A1:A61055").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Cells.Select
Selection.Copy
Application.CutCopyMode = True
ActiveSheet.ShowAllData
Selection.Delete Shift:=xlUp
ActiveSheet.Paste
End Sub

Lama -

When you execute the DELETE, you lose the COPY. You could paste the
results into another tab (worksheet). Then you could either delete the
original sheet, or if you have column widths, etc. that are important for
displaying the data, you could delete the data from the original sheet, then
copy from the new sheet and paste into the old one. Finally, delete the new
sheet.

--
Daryl S


"LAMA" wrote:

Hi.
I recorded a macro for the data entry group I work with that will save them
lots of time. The only step that doesn't work is the last one, which copies
data back from the clipboard into the current worksheet.

The goal was to split the first column into three, (it contains a number
with three nodes delimited by "-"), eliminate duplicate rows based on the
first column only, and renumber the shortened list.

Here's what I did:

Insert two columns to the right of column A.
Select column A.
Data - Text to Columns - Delimited by "-"
Delete columns B and C
Select column A
Data - Filter - Advanced Filter - Filter in place, Unique records only
(duplicate rows are now hidden)
Click on the cell in the upper left corner to select all data
Copy to clipboard
Data - Filter - Show all
Delete
Paste from Clipboard


The error is: run-time error '1004', Paste Method of Worksheet Class failed.

I'm not well versed in vb but I read everything I could find in the help on
paste method, tried adding a parameter or two, to no avail. Any help would
be greatly appreciated. Worst case, the macro leaves them with a blank
worksheet and the data on the clipboard ready to paste in.

Copied the macro in below in case that helps. We're using Excel 2003.

P.S. Is it possible to email a macro so I don't have to record it on
everyone's machine?

Thank you!!


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 1/25/2010 by Me
'
' Keyboard Shortcut: Ctrl+g
'
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
OtherChar _
:=" ", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Range("A1:A61055").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Cells.Select
Selection.Copy
Application.CutCopyMode = True
ActiveSheet.ShowAllData
Selection.Delete Shift:=xlUp
ActiveSheet.Paste
End Sub