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
– Shai Rado, Commented Apr 19, 2017 at 17:51

tigeravatar · Accepted Answer · 2017-04-19 18:02:27Z

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, 2017 at 18:02

tigeravatar

26.7k5 gold badges32 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4039065 Over a year ago

I was thinking about .CurrentRegion but I decided that the OP most likely has data in A1 and A2 that would muck it up.

GalacticPonderer Over a year ago

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!

Community · Accepted Answer · 2017-05-23 12:02:12Z

2

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

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Apr 19, 2017 at 18:07

user4039065

1 Comment

GalacticPonderer Over a year ago

Thank you very much, problem solved. I had read .Select was bad, but didn't know another solution. The pointer was very useful!

Shai Rado · Accepted Answer · 2017-04-19 18:04:20Z

1

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

answered Apr 19, 2017 at 18:04

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

1 Comment

GalacticPonderer Over a year ago

Perfect, thank you. This solved several problems I had that I didn't even realise I had :)

Collectives™ on Stack Overflow

VBA Range 1004 error

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related