VBA Range 1004 error

Question

I am using the following code:

Sub CSVParser()

Dim i As Integer
Dim x As Integer
Dim values As Range

Sheets("CSV Paste").Select    
Range("A3").Select

For i = 1 To Range("A3", Range("A3").End(xlDown)).Rows.Count        
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy

    Sheets("Working Sheet 1").Select
    Range("A1").Select 'problem code

    Do Until ActiveCell.Value = ""
        If ActiveCell.Value = "" Then
            Exit Do
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop

    ActiveSheet.Paste
    Sheets("CSV Paste").Select                               
    ActiveCell.Offset(1, 0).Select                
Next

End Sub

However, the line Range("A1").Select just after Sheets("Working Sheet 1").Select is kicking up a run-time error '1004'

Does anyone know why? I have rearranged this in every way I can think of an have typed it out from scratch again.

Anyway, you have way to many `Select` and `ActiveSheet` in your code. If you tell us what you are trying to achieve, maybe we can offer a better solution — Shai Rado, Apr 19 '17 at 17:51

score 2 · Answer 1 · answered Apr 19 '17 at 18:02

2

Give this version of your code a try:

Sub CSVParser()

    Dim wb As Workbook
    Dim wsCSV As Worksheet
    Dim wsWork As Worksheet

    Set wb = ActiveWorkbook
    Set wsCSV = wb.Sheets("CSV Paste")
    Set wsWork = wb.Sheets("Working Sheet 1")

    wsCSV.Range("A3").CurrentRegion.Copy wsWork.Cells(wsWork.Cells.Count, "A").End(xlUp).Offset(1)

End Sub

answered Apr 19 '17 at 18:02

tigeravatar

26,199
5
30
38

1

I was thinking about .CurrentRegion but I decided that the OP most likely has data in A1 and A2 that would muck it up. – Apr 19 '17 at 18:13
tigeravatar, thank you. Jeeped was correct however, and I'm am trying to parse information out of a CSV with irritating headers. You answer was really appreciated though! – GalacticPonderer Apr 20 '17 at 06:07

score 2 · Answer 2 · edited May 23 '17 at 12:02

Using .Select and .Activate is not considered 'best practice'. See How to avoid using Select in Excel VBA macros. Yes, using the code from the macro recorder is a good place to start but you have to get away from the practice at some point.

Performing bulk operations is preferred to looping through an indeterminate number of rows or columns.

Option Explicit

Sub CSVParser()

    Dim lastCol As Long

    With Worksheets("CSV Paste")
        With .Range(.Cells(3, "A"), .Cells(.Rows.Count, "A").End(xlUp))
            lastCol = .CurrentRegion.Columns.Count
            With .Resize(.Rows.Count, lastCol)
                .Copy Destination:=Sheets("Working Sheet 1").Range("A1")
            End With
        End With
    End With

End Sub

Thank you very much, problem solved. I had read .Select was bad, but didn't know another solution. The pointer was very useful! — GalacticPonderer, Apr 20 '17 at 06:04

score 1 · Accepted Answer · answered Apr 19 '17 at 18:04

I think this is what you are trying to achieve (without all the unnecessary Select):

Option Explicit

Sub CSVParser()

Dim i As Long
Dim x As Long
Dim LastRow As Long
Dim PasteRow As Long

With Sheets("CSV Paste")
    LastRow = .Range("A3").End(xlDown).Row
    For i = 3 To LastRow
        PasteRow = Sheets("Working Sheet 1").Cells(Sheets("Working Sheet 1").Rows.Count, "A").End(xlUp).Row
        .Range(.Range("A" & i), .Range("A" & i).End(xlToRight)).Copy Destination:=Sheets("Working Sheet 1").Range("A" & PasteRow + 1)
    Next i
End With

End Sub

Perfect, thank you. This solved several problems I had that I didn't even realise I had :) — GalacticPonderer, Apr 20 '17 at 12:25

VBA Range 1004 error

3 Answers3