VBA Code assistance for copy and past between 2 worksheets

[bootj1234]

I am trying to copy the dynamic range of data from one worksheet and place it into StartCell range in the destination spreadsheet with the StartCell range beginning in Cell ("A11").

The copying of the dynamic range for the data within the copy worksheet is working. I am struggling and need assistance with the code that will allow me to set Cell ("A11") in the destination worksheet as the starting cell for pasting the data from the dynamic range of the copy worksheet.

Thanks for the assistance with the correct code.

-John

- - - - - - - - -

Sub Copy_EVMStartRport_2()

'Find the last used row in both sheets and copy and paste data below existing data.

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim StartCell As Range
Set StartCell = Range("A11")

'Set variables for copy and destination sheets
Set wsCopy = ThisWorkbook.Worksheets("EVM-StartReport")
Set wsDest = ThisWorkbook.Worksheets("EVM-FinishedReport")

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Copy & Paste Data into range on destination worksheet using "A11" as the StartCell
wsCopy.Range("A5:BB" & lCopyLastRow).Copy _
wsDest.StartCell

End Sub

 

[Jeffrey Mahoney]

When you post code, could you please surround it with the CODE tags. It's easier to read and copy.

Please try this:

Sub Copy_EVMStartRport_2()


'Find the last used row in both sheets and copy and paste data below existing data.


  Dim TWB As Workbook
  Dim wsCopy As Worksheet
  Dim wsDest As Worksheet
  Dim lCopyLastRow As Long
  Dim StartCell As Range
  
  
  'Set variables for copy and destination sheets
  Set TWB = ThisWorkbook
  Set wsCopy = TWB.Worksheets("EVM-StartReport")
  Set wsDest = TWB.Worksheets("EVM-FinishedReport")
  
  '1. Find last used row in the copy range based on data in column A
  lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
  'Find the next blank row and set the range for the Finished Report
  Set StartCell = wsDest.Cells(wsDest.Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
  'or just use range A11
  Set StartCell = wsDest.Range("A11")
  
  '2. Copy & Paste Data into range on destination worksheet using "A11" as the StartCell
  wsCopy.Range("A5:BB" & lCopyLastRow).Copy StartCell


End Sub

 

[bootj1234]

Hi Jeffrey,

Thanks for the very quick reply and assistance. Your code worked out great!

Best regards - John