PROBLEMS WITH MACROS

  • Thread starter Thread starter Neil Holden
  • Start date Start date
N

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.
 
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
 
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
 
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 :)
 
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
 
Back
Top