Import data from another Worksheet

[David T]

Hello All-

I have a code that pulls data from another worksheet into the worksheet that
i am working on, but the only problem is that the code pulls exactly 100
lines of data from the other worksheet. I need to modify the code to where
it'll pull the last row of data whether it's 5 lines , 100 lines or 500
lines. Can anyone help?

The code is written below

Sub Retrieve_Info()
P = "C:\Documents and Settings\David Truong\Desktop"
f = "Book2.xlsm"
s = "Sheet1"

Application.ScreenUpdating = False
For r = 1 To 100
For C = 1 To 4
a = Cells(r, C).Address
Cells(r, C) = GetValue(P, f, s, a)
Next C
Next r
Application.ScreenUpdating = True
End Sub


Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

 

[Dave O]

Here's one way: I've added some lines of code to your routine, and
marked them with '* * * * * to distinguish them.

Sub Retrieve_Info()
dim LastRow as Long '* * * * * added line: declare a variable
P = "C:\Documents and Settings\David Truong\Desktop"
f = "Book2.xlsm"
s = "Sheet1"

'* * * * * long added line here: the underscores tell the compiler to
concatenate these three rows.
LastRow = Mid(ActiveSheet.UsedRange.Address, _
InStr(InStr(1, ActiveSheet.UsedRange.Address, ":$") + 2, _
ActiveSheet.UsedRange.Address, "$") + 1,
Len(ActiveSheet.UsedRange.Address))

Application.ScreenUpdating = False
For r = 1 To LastRow '* * * * * this line changed

Everything else stays the same. Note: because the typed lines in this
post may be wrapped by your screen display, the long line above that
calculates LastRow may be wrapped in the wrong place, and generate a
compile error. If that happens, edit those lines of code so the first
line of code starts with LastRow = and ends with an underscore; the
second line should start with InStr( and end with an underscore; the
third line should start with ActiveSheet and end with Address))

Let us know how it goes!
DaveO

 

[David T]

Hey Dave O

Thanks for your reply, but when I added the code, it only pulled the first
line of data from the other worksheet although there were at least 100 lines.
It just did not pull data beyond the first line.