PROBLEMS WITH MACROS

[Neil Holden]

Hello, I need a macro, when the button is pressed I want a range of cells to
copy and paste into a different excel sheet and always look for the last
blank row so that each time it will automatically insert.

Please help.

 

[Jacob Skaria]

Hi Neil

Try the below. Adjust the range and sheet names to suit..

Sub Macro()
Dim lngRow As Long, wsDest As Worksheet, rngTemp As Range

Set wsDest = Sheets("Sheet2") 'Destination sheet
Set rngTemp = Sheets("Sheet1").Range("A1:A3")
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)

End Sub

If this post helps click Yes

 

[Mike H]

Neil,

This would copy the selected cells and paste them into the first empty row
in column A of sheet 2

lastrow = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
Selection.Copy Destination:=Sheets("Sheet2").Range("A" & lastrow)

Mike

 

[Neil Holden]

Hello, thanks for your help on this, i need to information to go to an
external excel sheet and not the same. Not sure if this will be a simple
change?

Neil.

P.S Mike thanks for your help with ALL my questions

 

[Jacob Skaria]

If 'External' means another open workbook then try the below

'Change the workbook name as required. Only saved workbook will have the
extension.
Sub Macro()
Dim lngRow As Long, wsDest As Worksheet, rngTemp As Range

Set wsDest = Workbooks("workbookname.xls").Sheets("Sheet2") 'Destination sheet
Set rngTemp = Sheets("Sheet1").Range("A1:A3")
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)

End Sub


If you mean closed workbook; try the below..

Sub Macro()
Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A1:A3")

Set wbBook = Workbooks.Open("<fullpath>\<filename.ext>")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
rngTemp.Copy wsDest.Range("A" & lngRow)

wbBook.Close True

End Sub


If this post helps click Yes