PROBLEMS WITH MACROS
 

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.

PROBLEMS WITH MACROS
 

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
---------------
Jacob Skaria


"Neil Holden" wrote:

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.

PROBLEMS WITH MACROS
 

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" wrote:

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.

PROBLEMS WITH MACROS
 

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

"Mike H" wrote:

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" wrote:

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.

PROBLEMS WITH MACROS
 

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
---------------
Jacob Skaria


"Mike H" wrote:

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" wrote:

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.