CODEWITHSUNDEEP
vbaexcel
GalacticPonderer
GalacticPonderer

Reputation: 547

VBA Range 1004 error

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.

Upvotes: 1

Views: 1263

Answers (3)

user4039065
user4039065

Reputation:

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

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 2

Related Questions