Cartolo
06-12-2017, 04:56 PM
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
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