Get data reference to last row

D

DavidH56

Hello,

I've had a great deal of success with assistance from several experts on
this site. I thank those experts who provide their much needed assistance to
those like me, somewhat familiar with vba be still in need of much
improvement. I am currently using a reference to A1:K500 in this bit of
fabulous code I've picked up form this site (I believe from Mr. De Bruin) but
what I really what is through the end of the used range or to the last row.
the code is as follows:

Sub GetLastReportsData()
Dim MyPath As String
Dim FilesInPath As String
Dim sh As Worksheet
Dim MyFiles() As String
Dim FNum As Long
Dim rnum As Long
Dim destrange As Range

MyPath = Location & "\Prior Report\" ' <<<< Change

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls*")
If FilesInPath = "" Then
MsgBox "No files found in Prior Report's Folder"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Olddata"
Columns("E:G").Select
Selection.NumberFormat = "[$-409]dd-mmm-yy"

' 'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = lastrow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
'sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(FNum), "EventWS", "A1:K500", destrange,
False, False
Next
End If

'Columns("C:C").Delete

namefixer

Range("D:F,I:I").Select

Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("D2").Select

CleanUp:
Application.ScreenUpdating = True

End Sub


Thanks once again in advance for your assistance.
 
B

Barb Reinhardt

You may be able to use something like this

myWS.usedrange.address in place of A1:K500. I'm not sure what the
worksheet name is you're working with, but you should be able to figure that
out.
 
R

Ron de Bruin

There is a example for this on the site
http://www.rondebruin.nl/copy3.htm

Read the information above the first macro


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




DavidH56 said:
Hello,

I've had a great deal of success with assistance from several experts on
this site. I thank those experts who provide their much needed assistance to
those like me, somewhat familiar with vba be still in need of much
improvement. I am currently using a reference to A1:K500 in this bit of
fabulous code I've picked up form this site (I believe from Mr. De Bruin) but
what I really what is through the end of the used range or to the last row.
the code is as follows:

Sub GetLastReportsData()
Dim MyPath As String
Dim FilesInPath As String
Dim sh As Worksheet
Dim MyFiles() As String
Dim FNum As Long
Dim rnum As Long
Dim destrange As Range

MyPath = Location & "\Prior Report\" ' <<<< Change

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls*")
If FilesInPath = "" Then
MsgBox "No files found in Prior Report's Folder"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Olddata"
Columns("E:G").Select
Selection.NumberFormat = "[$-409]dd-mmm-yy"

' 'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = lastrow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
'sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(FNum), "EventWS", "A1:K500", destrange,
False, False
Next
End If

'Columns("C:C").Delete

namefixer

Range("D:F,I:I").Select

Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("D2").Select

CleanUp:
Application.ScreenUpdating = True

End Sub


Thanks once again in advance for your assistance.
--
By persisting in your path, though you forfeit the little, you gain the
great.


__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
D

DavidH56

Thanks for the quick response Barb. I tried your code but it did not work.
I'm missing values from formulas in coulmn K.

Thanks
--
By persisting in your path, though you forfeit the little, you gain the
great.



Barb Reinhardt said:
You may be able to use something like this

myWS.usedrange.address in place of A1:K500. I'm not sure what the
worksheet name is you're working with, but you should be able to figure that
out.

DavidH56 said:
Hello,

I've had a great deal of success with assistance from several experts on
this site. I thank those experts who provide their much needed assistance to
those like me, somewhat familiar with vba be still in need of much
improvement. I am currently using a reference to A1:K500 in this bit of
fabulous code I've picked up form this site (I believe from Mr. De Bruin) but
what I really what is through the end of the used range or to the last row.
the code is as follows:

Sub GetLastReportsData()
Dim MyPath As String
Dim FilesInPath As String
Dim sh As Worksheet
Dim MyFiles() As String
Dim FNum As Long
Dim rnum As Long
Dim destrange As Range

MyPath = Location & "\Prior Report\" ' <<<< Change

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls*")
If FilesInPath = "" Then
MsgBox "No files found in Prior Report's Folder"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Olddata"
Columns("E:G").Select
Selection.NumberFormat = "[$-409]dd-mmm-yy"

' 'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = lastrow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
'sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(FNum), "EventWS", "A1:K500", destrange,
False, False
Next
End If

'Columns("C:C").Delete

namefixer

Range("D:F,I:I").Select

Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("D2").Select

CleanUp:
Application.ScreenUpdating = True

End Sub


Thanks once again in advance for your assistance.
Click to expand...
Click to expand...
 
R

Ron de Bruin

Check out also the Merge add-in
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




DavidH56 said:
Hello,

I've had a great deal of success with assistance from several experts on
this site. I thank those experts who provide their much needed assistance to
those like me, somewhat familiar with vba be still in need of much
improvement. I am currently using a reference to A1:K500 in this bit of
fabulous code I've picked up form this site (I believe from Mr. De Bruin) but
what I really what is through the end of the used range or to the last row.
the code is as follows:

Sub GetLastReportsData()
Dim MyPath As String
Dim FilesInPath As String
Dim sh As Worksheet
Dim MyFiles() As String
Dim FNum As Long
Dim rnum As Long
Dim destrange As Range

MyPath = Location & "\Prior Report\" ' <<<< Change

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls*")
If FilesInPath = "" Then
MsgBox "No files found in Prior Report's Folder"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Olddata"
Columns("E:G").Select
Selection.NumberFormat = "[$-409]dd-mmm-yy"

' 'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = lastrow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
'sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(FNum), "EventWS", "A1:K500", destrange,
False, False
Next
End If

'Columns("C:C").Delete

namefixer

Range("D:F,I:I").Select

Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("D2").Select

CleanUp:
Application.ScreenUpdating = True

End Sub


Thanks once again in advance for your assistance.
--
By persisting in your path, though you forfeit the little, you gain the
great.


__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
D

DavidH56

Thanks Ron this is a lot of good info. It worked out just great when i
reference cells A:K.
--
By persisting in your path, though you forfeit the little, you gain the
great.



Ron de Bruin said:
Check out also the Merge add-in
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




DavidH56 said:
Hello,

I've had a great deal of success with assistance from several experts on
this site. I thank those experts who provide their much needed assistance to
those like me, somewhat familiar with vba be still in need of much
improvement. I am currently using a reference to A1:K500 in this bit of
fabulous code I've picked up form this site (I believe from Mr. De Bruin) but
what I really what is through the end of the used range or to the last row.
the code is as follows:

Sub GetLastReportsData()
Dim MyPath As String
Dim FilesInPath As String
Dim sh As Worksheet
Dim MyFiles() As String
Dim FNum As Long
Dim rnum As Long
Dim destrange As Range

MyPath = Location & "\Prior Report\" ' <<<< Change

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls*")
If FilesInPath = "" Then
MsgBox "No files found in Prior Report's Folder"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Add worksheet to the Activeworkbook and use the Date/Time as name
Set sh = ActiveWorkbook.Worksheets.Add
sh.Name = "Olddata"
Columns("E:G").Select
Selection.NumberFormat = "[$-409]dd-mmm-yy"

' 'Fill the array(myFiles)with the list of Excel files in the folder
FNum = 0
Do While FilesInPath <> ""
FNum = FNum + 1
ReDim Preserve MyFiles(1 To FNum)
MyFiles(FNum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If FNum > 0 Then
For FNum = LBound(MyFiles) To UBound(MyFiles)

'Find the last row with data
rnum = lastrow(sh)

'create the destination cell address
Set destrange = sh.Cells(rnum + 1, "A")

' Copy the workbook name in Column E
'sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)

'Get the cell values and copy it in the destrange
'Change the Sheet name and range as you like
'Change the Sheet name and range as you like
GetData MyPath & MyFiles(FNum), "EventWS", "A1:K500", destrange,
False, False
Next
End If

'Columns("C:C").Delete

namefixer

Range("D:F,I:I").Select

Selection.NumberFormat = "[$-409]d-mmm-yy;@"
Range("D2").Select

CleanUp:
Application.ScreenUpdating = True

End Sub


Thanks once again in advance for your assistance.
--
By persisting in your path, though you forfeit the little, you gain the
great.


__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 3961 (20090325) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help with merging worksheets 3
Copy part of text file 4
NEED HELP!!!! 1
Macro Help!!!!!! 2
Open CSV File 4
Import Data from Multiple Sources 8
modification of auto-multiple workbook macro, Ron DeBruin (Copy4) 10
Copy non-contiguous cells from all workbooks in folder 8

Top