VBA Code assistance for copy and past between 2 worksheets

bootj1234

Board Regular
Joined
Aug 27, 2012
Messages
85
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
When you post code, could you please surround it with the CODE tags. It's easier to read and copy. :)

Please try this:

Code:
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

Board Regular
Joined
Aug 27, 2012
Messages
85
Hi Jeffrey,

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

Best regards - John
 

Watch MrExcel Video

Forum statistics

Threads
1,108,923
Messages
5,525,653
Members
409,658
Latest member
Yardcell

This Week's Hot Topics

Top