Getting Error message on Macro

ariel20029

Board Regular
Joined
Jun 20, 2013
Messages
97
Hi, I am getting an undefined sub error message on this macro I modified. I am guessing its something very simple I did causing the problem. The macro is to copy and paste from one spreadsheet to another 2 different ranges ( offsetting Journal entries). The original code was to copy from one workbook to another, I just modified it from one spreadsheet to another, but I broke it.. Can you see what I did wrong..? Also I need to copy paste special not just copy.. is there a simple change for that modification?
thanks a million.
Sharon

this is the main code I changed



Set wsCopy = Worksheets("JE Setup")
Set wsDest = Worksheets("Journal Entry")




<tbody>
</tbody>
Sub Copy_Paste_Below_Last_Cell()

'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 lDestLastRow As Long
'Set variables for copy and destination sheets
Set wsCopy = Worksheets("JE Setup")
Set wsDest = Worksheets("Journal Entry")
'1. Find last used row in the copy range based on data in column P
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "P").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("P4:Z" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)
'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "P").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
'3. Copy & Paste Data
wsCopy.Range("AB4:AL" & lCopyLastRow).Copy _
wsDest.Range("A" & lDestLastRow)

<colgroup><col span="7"><col></colgroup><tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,860
Office Version
365
Platform
Windows
Which row is highlighted when you click Debug?
 

Forum statistics

Threads
1,085,838
Messages
5,386,258
Members
401,989
Latest member
romandavis

Some videos you may like

This Week's Hot Topics

Top