Help with ranges/skip hidden rows in VBA Code

[Cartolo]

Hi All. I am using this code to copy data from 3 columns from workbook 'x' to workbook 'y'.

I need to copy and paste the workbook rows from one another but I get the error "1004": Method 'Range' of object'_Global' failed right around

Range("Q3:R3" & i +1)

.

Another issue when I try to skip the hidden rows from book 'x' so they do not appear in book 'y'. I do not see the rows removed. Here is the code below. Thank you.

Sub GetDataDemo()
    Dim FilePath$
    Dim i As Long
    Const FileName$ = "EMS.xlsx"
    Const SheetName$ = "PO"
    FilePath = "C:\Users\DD\Desktop\"


    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
        Exit Sub
If ActiveCell.EntireRow.Hidden = True Then
ActiveCell.Offset(1, 0).Select
End If

    End If
    For i = 3 To 500
        Range("Q3:R3" & i +1) = GetData(FilePath, FileName, SheetName, Range("Y2:A2" & i))
    Next i
    ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Rng)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & Rng.Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function

[SamT]

This is just wrong

Range("Q3:R3" & i +1)

It results in: Range("Q3:R34") all the way up to Range("Q3:R3501")

I think you want

Range(Cells(i, "Q"), Cells(i + 1, "R"))

which is the same as

Range("Q" & i & ":R" & i+1)

But even that is a two row by two column range

Rethink the other mentioned range also (Y:A)

[Bob Phillips]

This might get you closer

Sub GetDataDemo()
    Const FileName As String = "EMS.xlsx"
    Const SheetName As String = "PO"
    FilePath = "C:\Users\DD\Desktop\"
    Dim wb As Workbook
    Dim this As Worksheet
    Dim i As Long, ii As Long

    Application.ScreenUpdating = False
    
    If IsEmpty(Dir(FilePath & FileName)) Then
    
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
    Else
    
        Set this = ActiveSheet
        
        Set wb = Workbooks.Open(FilePath & FileName)
        
        With wb.Worksheets(SheetName).Range("A2:Y2")
        
            ii = 3
            For i = 3 To 500
        
                If Not .Rows(i).Hidden Then
                
                    .Cells(i).Copy
                    this.Range("R3").Cells(ii).Paste
                    ii = ii + 1
                End If
            Next i
        End With
    End If
    
    ActiveWindow.ScreenUpdating = True
End Sub

[Cartolo]

It works fine until the ranges. I get this error: Run-time error '1004': Method 'Range' of object '_Global' failed.

Around this section.

With wb.Worksheets(SheetName).Range("A2" & i)

[Cartolo]

It works fine until the ranges. I get this error: Run-time error '1004': Method 'Range' of object '_Global' failed.

Around this section.

With wb.Worksheets(SheetName).Range("A2" & i)

[Bob Phillips]

I can't see that in my code.

[Cartolo]

I meant the

With wb.Worksheets(SheetName).Range("A2:Y2") 

creates the error.