0

I have code to copy a Worksheet A, columns A:C (no set row quantities, this will be re-used and the quantities will change) and paste to the first blank row in the same workbook, different sheet, Worksheet B (this also has no set row quantities and will change).

Worksheet B has a formula in the same columns that I want to paste to that returns "" if there is no data. I think VBA is seeing the "" and assuming there is data there; however, it is not pasting even to lines without said formula. 

Sub Copy_Created_Fringe_Accounts()
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim DestSheet As Worksheet
    Dim LastRow As Long

'Source sheet and range
    Set SourceRange = Sheets("CREATED FRINGE ACCTS").Range("A2:C500")

'Destination sheet and range
    Set DestSheet = Sheets("99 BUDGET WORKSHEET")

'Last Row
    LastRow = DestSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'Copy and paste to destination
    Set DestRange = DestSheet.Range("A" & LastRow + 1)
    SourceRange.Copy DestRange

End Sub

Nothing happens when I run it. I expect to see the data from Worksheet A copied to Worksheet B, starting at the first available empty row.

I am fairly new to VBA so any help/understanding is appreciated.

Miles Fett
  • 711
  • 4
  • 17
Christine D
  • 1
  • 3
  • Have you used `Debug.Print LastRow` to determin what it is returning? – Dean Aug 14 '19 at 21:41
  • I have not; I am not familiar with that or how it is used; is that something I would just add to my existing VBA (what is included in above)? – Christine D Aug 14 '19 at 21:58
  • Add it after you set `LastRow`. It will print a number to the Immediate Window in your editor. But, why don't you adjust your formula in your cell to return something like "PASTE" instead of ""?? – Dean Aug 15 '19 at 06:09

1 Answers1

0

Finding the last row

Try using UsedRange to find the last used row as this is safer than using Find.

LastRow = DestSheet.UsedRange.Rows.Count

A side note

If your code resides in the same place as these worksheets then I would recommend using their code name. This will protect you from running into an error if the sheet doesn't exist.

Miles Fett
  • 711
  • 4
  • 17
  • No luck. I did run a simple VBA code to make sure there were no other issues going on. Not sure if I should trash my code and start from scratch or if there is some small something I am missing. – Christine D Aug 14 '19 at 23:38
  • I've tested this on numerous different worksheets and it's run correctly. Do you have an example of the worksheets you're using? – Miles Fett Aug 15 '19 at 13:47
  • Pardon my lack of knowledge about how SO works - are you asking me for the file, which I can share - if I could figure out how to upload or are you asking for screenshots? – Christine D Aug 16 '19 at 18:02