Excel VBA Code for Copy & Paste Without Header, Paste in Last Row

jxj_00

New Member
Joined
Oct 1, 2020
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi, please help as I am very new to VBA.

I need to copy and paste from Workbook 1, Worksheet 1 to Workbook 2, Worksheet 2.
There are 2 main things I need to achieve with the code.
1. Copy from WB1, WS1 from row 4 to WB2, WS2 row 4 (As I have headers & macro buttons above)
2. Paste to WB2, WS2 at the last empty row

The code runs successfully.
However, it tells me to select a destination and press enter. However, the code I have written is supposed to add it to last empty row.

Please help and thank you in advance!

VBA Code:
Sub Copy_Without_Header()

'Delete Empty Rows'
On Error Resume Next
Range("B3:B" & Workbooks("WB1.xlsm").Worksheets("WS1"). _
UsedRange.Rows.Count).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy without header'
Workbooks("WB1.xlsm").Worksheets("WS1"). _
Range("A4").CurrentRegion.Offset(1, 0).Resize(Range("A4").CurrentRegion.Rows.Count - 1).Copy

'Paste in last empty row'
Workbooks("WB2.xlsm").Worksheets("WS2"). _
Range("A4").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValuesAndNumberFormats

End Sub

1601557738183.png
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,440
Members
410,684
Latest member
LakTik
Top