• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Last row error

Y

ysherriff

Member
can you check to see where i am going wrong with this last row error. Everything is working except that.

I apprecaite your help. Will also see if i can find solutions.
 

Attachments

  • vba-macro-to-copy-data-from-multiple-files v.04.xls
    59.5 KB · Views: 2
ysherriff said:
can you check to see where i am going wrong with this last row error. Everything is working except that.

I apprecaite your help. Will also see if i can find solutions.
Click to expand...
Hi,

The first code line below will error out because you haven't SET Sh to anything. You have just opened this workbook:-

C:\Users\yhs0004\Desktop\Various Files\3D Discover Define Drive\3D DISCOVER DEFINE DRIVE REPORT.xls and activated a worksheet called Daily Activity so I guess you really want the second line below or you could use the SET statement to SET sh as Daily Activity and use your original line.


Code: 
LastRow = sh.Range("D500").End(xlUp).Row

LastRow = Sheets("Daily Activity").Range("D500").End(xlUp).Row
 
dumb question mike but how do i select the ranges now that this work i want to select only range b7:y7 with the last row. once again, learning vba and will see if i can add to your code.


LastRow = Sheets("Daily Activity").Range("D500").End(xlUp).Row ---this is working

from here the selection of the ranges are not working
sh.Range(Range("b7").Address & ":" & sh.Range("y7" & LastRow).Address).Copy
 
ysherriff said:
dumb question mike but how do i select the ranges now that this work i want to select only range b7:y7 with the last row. once again, learning vba and will see if i can add to your code.


LastRow = Sheets("Daily Activity").Range("D500").End(xlUp).Row ---this is working

from here the selection of the ranges are not working
sh.Range(Range("b7").Address & ":" & sh.Range("y7" & LastRow).Address).Copy
Click to expand...
Hi,

It's much simpler than that, try it this way.

Code: 
sh.Range("B7:Y" & lastrow).Copy
 
it just skips right over the code. Conceptually, the macro is correct but I am missing something.

this should be a very simple process but i am lost on how to copy. my brains are fried....lol
 

Attachments

  • vba-macro-to-copy-data-from-multiple-files v.04.xls
    60 KB · Views: 3
ysherriff said:
it just skips right over the code. Conceptually, the macro is correct but I am missing something.

this should be a very simple process but i am lost on how to copy. my brains are fried....lol
Click to expand...
Hi,

You've made exactly the same mistake with this line

sh.Range("B7:Y7" & LastRow).Copy

Sh is nothing, you haven't SET it. You have to decide what you want to do, SET it with a line like this and use your original line.

set sh=sheets("Wahtever")

or do it like this

sheets("Whatever").Range("B7:Y7" & LastRow).Copy

It's very difficult for me to do it all for you because it means creating workbooks and paths with the same structure as yours but I can continue to guide you through the process.
 
its weird but it worked. on anothe consolidated macro i did not have to declare the sheet. but the sheets were on all the same workbook. this macro the sheets are on different workbooks that needs to be consolidated. Chandoo's consolidated macro works but is not for dynamic range. it is strictly for fixed ranges. i was trying to modify his macro for dynamic ranges.

what about this. do i need to declare this range as well. i really appreciate your help.

TargetSh.Range(DestCell.Address).Select

below is the full macro again:
-----------------------------------
Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String, ActualRow As Long, sh As Worksheet, TargetSh As Worksheet
Dim DestCell As Range, LastRow As Long

strListSheet = "List"
Application.ScreenUpdating = False
Application.DisplayAlerts = False


'Delete the sheet "SUMMARY" if it exists
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("MasterData").Delete
On Error GoTo 0
Application.DisplayAlerts = True

On Error Resume Next
Set TargetSh = Worksheets("MasterData")
On Error GoTo 0
If TargetSh Is Nothing Then
Set TargetSh = Worksheets.Add(before:=Sheets(1))
TargetSh.Name = "MasterData"
Else
TargetSh.Cells.Clear
End If

Set DestCell = TargetSh.Range("A2")
Set DestCell = DestCell.Offset(1, 0)

On Error GoTo ErrH
Sheets(strListSheet).Select
Range("b2").Select

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> ""

strFileName = ActiveCell.Offset(0, 1)
'strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
'strWhereToCopy = ActiveCell.Offset(0, 4).Value
'strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)


Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook
Set sh = Sheets("Daily Activity")
'activate daily activity sheet
Sheets("Daily Activity").Activate

'select usedrange to copy
LastRow = Sheets("Daily Activity").Range("D500").End(xlUp).Row
If LastRow > 1 Then
sh.Range("B7:Y" & LastRow).Copy
TargetSh.Range(DestCell.Address).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set DestCell = DestCell.Offset(LastRow - 8)
End If

'activates master summary sheet
currentWB.Activate

Loop

Exit Sub
Application.DisplayAlerts = True
Application.ScreenUpdating = True
ErrH:
MsgBox "It seems some file was missing. The data copy operation is not complete."
Exit Sub
End Sub
 
You must log in or register to reply here.
Back
Top