• 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
I am trying to copy and paste data to the last row and getting an error message at this code. Everything is working fine until I get to this section.

Code: 
Set DestCell = DestCell.Offset(LastRow - 8)

The full code for this loop is below:

Code: 
  'activate template
  wkbkTemp.Activate
 
  On Error Resume Next
  Set TargetSh = Worksheets("Competitor")
  On Error GoTo 0
 
  Sheets("Competitor").Activate
 
  Set DestCell = TargetSh.Range("COMPETITORS_START_CELL")
  Set DestCell = DestCell.Offset(1, 0)
 
  TargetSh.Activate
  Rows("2:" & Rows.Count).ClearContents
 
  'activate generator
  wkbkGen.Activate
 
  For Each MyCell In MyRange
  If MyCell.Value = "" Then Exit For ' this exits when you have a blank cell
 
 
  Worksheets("Disch by Sender and Receiver").Activate
 
  'filter the sheet
  Range("a5").Select
  ActiveSheet.ShowAllData
  If ActiveSheet.AutoFilterMode = False Then
  Selection.AutoFilter
  End If
 
  'select the range and autofilter based on hospital name
  ActiveSheet.Range("DISCH_BY_SENDER_RECEIVER_TBL").AutoFilter Field:=hospitalNameIndex, Criteria1:=MyCell
 
  ActiveSheet.Range("DISCH_BY_SENDER_RECEIVER_TBL").AutoFilter Field:=16, Criteria1:=Array( _
  "1", "2", "3"), Operator:=xlFilterValues
 
  ActiveCell.CurrentRegion.Select
 
  Set tbl = ActiveCell.CurrentRegion
  tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, tbl.Columns.Count).Select
  Selection.Copy
 
  'activate template
  wkbkTemp.Activate
 
  TargetSh.Activate
 
  LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
 
  TargetSh.Range(DestCell.Address).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
 
  Set DestCell = DestCell.Offset(LastRow - 8)
 
 
 
  Next MyCell

I have attached the spreadsheet with the code. is it my LastRow argument? Is it positioned appropriately in the loop. I am at a lost here

Thanks for your help.
 
Hi ysherriff

This code would be easier to follow if we did not have to recreate it. You said you attached a file but there is nothing. Please attach a file.

Take care

Smallman
 
At a guess try
Code: 
Set DestCell = TargetSh.Range("COMPETITORS_START_CELL")
  Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
 
Hi ,

The problem may be because of the value of LastRow ; if the value of LastRow is 8 or less , then you will be trying to backup to an invalid row number.

When you get the error , click on Debug , and in the Immediate window , type in :

?LastRow

and see what value is displayed ; if it is 8 or less , then you need to decide what is to be done ; why are you subtracting the constant value of 8 from the derived value of LastRow ?

Narayan
 
ok. thanks for all of your help. I found out where the bug is but don't know exactly how to fix this. I have provided the link for the file. also I am attaching a jpeg so you can see the issue.

https://www.dropbox.com/s/fo2kncdemj76iym/Territory Management Generator v.06.zip?dl=0


Let me explain. The formula code to find the last row works perfectly

Code: 
LastRow = ActiveSheet.Range("A300").End(xlUp).Row


but when it attempts to copy and paste into the next available row, this is where the issue lie and it is in this code below.

Code: 
  TargetSh.Range(DestCell.Address).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False
 
  Set DestCell = DestCell.Offset(LastRow)


What is happening is the TargetSh.Range(DestCell.Address).Select is adding the value from the previous lastrow and the current lastrow.

I gave an example in the jpeg attached where the previous last row was 7 and the current last row is 14, so it is pasting on row 21.

I hope I am making myself clear. I just don't know how to fix the above code and I know that is where the error lies. I am just trying to move the copy the data to the last row and just don't know what needs modification. Any help is appreciate.

The DestCell is defined as

Code: 
  Set DestCell = TargetSh.Range("COMPETITORS_START_CELL")
  Set DestCell = DestCell.Offset(1, 0)



upload_2014-12-30_22-11-41.png
 
Pike,

Thanks for your help. The code you provided earlier helped.

I replaced this code:

Code: 
Set DestCell = DestCell.Offset(LastRow)

with this code

Code: 
Set DestCell = TargetSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

and it worked and is more efficient and error prone. Thank you very much.....!!!

you made my day!!!
 
brillant!
If i get a chance i look a little deeper into your syntax as there are changes that could be made; like using the "With" statement instead of "Activate"
 
You must log in or register to reply here.
Back
Top