Billsy
12-23-2020, 03:19 PM
All the best everyone. First post from a VBA newb. I'm using the following code to copy the user selected rows from sheet1 and paste into the next available row in sheet2. I would like to make a slight change and ad a few functions if anyone has the time to help me out.
I would like to paste values only into sheet2
then remove all instances of the pipe symbol followed by space (| ) from column K only.
Then confirm character count in column K cells are 80 or less.
Then export sheet2 to CSV.
I hope this isn't too much to ask.
Many thanks
Billsy
Sub CopySelectedRows()
Dim rCopy As Range
Dim lAreas As Long
On Error Resume Next
Set rCopy = Application.InputBox("Select Rows to copy. " _
& "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , , 8)
On Error GoTo 0
If rCopy Is Nothing Then Exit Sub 'Hit Cancel
If rCopy.Columns.Count <> Columns.Count Then
MsgBox "Please select entire row(s)"
Run "CopySelectedRows"
Else
For lAreas = 1 To rCopy.Areas.Count
rCopy.Areas(lAreas).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next lAreas
End If
End Sub
I would like to paste values only into sheet2
then remove all instances of the pipe symbol followed by space (| ) from column K only.
Then confirm character count in column K cells are 80 or less.
Then export sheet2 to CSV.
I hope this isn't too much to ask.
Many thanks
Billsy
Sub CopySelectedRows()
Dim rCopy As Range
Dim lAreas As Long
On Error Resume Next
Set rCopy = Application.InputBox("Select Rows to copy. " _
& "Use Ctrl for non-contiguous rows", "COPY ROWS", Selection.Address, , , , , 8)
On Error GoTo 0
If rCopy Is Nothing Then Exit Sub 'Hit Cancel
If rCopy.Columns.Count <> Columns.Count Then
MsgBox "Please select entire row(s)"
Run "CopySelectedRows"
Else
For lAreas = 1 To rCopy.Areas.Count
rCopy.Areas(lAreas).Copy Sheet2.Cells(Rows.Count, 1).End(xlUp)(2, 1)
Next lAreas
End If
End Sub