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!
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