CODEWITHSUNDEEP
vbaexcel
Columbus
Columbus

Reputation: 3

Excel copy data from one sheet to another on worksheet refresh with vba

I'd like to copy data from 'Sheet1' in a specific cell to the next available empty cell in 'Sheet2' such that changes (F9) result in data being copied from Sheet1 and a columne of data being populated in Sheet2.

So far I have the below, which throws an error around: (.Rows.Count

Is it possible to popluate a column in Sheet2 every time the workbook is refreshed on 'F9'?

'In this example I am Copying the Data from Sheet1 (Source) to Sheet2              (Destination)
Private Sub worksheet_calculate()
'Method 2
'Copy the data
Sheets("Sheet1").Range("I1").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Sheet2.Cells(.Rows.Count, "A").End(xlUp).Row
'Paste in the target destination
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Upvotes: 0

Views: 2219

Answers (3)

Shai Rado
Shai Rado

Reputation: 33672

Try the code below, it will copy Cell I1 from "Sheet1" to next available row in Column A in "Sheet 2".

It's better if you avoid using Activate and ActiveSheet, instead you can just Copy>Paste in one line (as in the code below)

Not sure why you want this code in the worksheet_calculate event.

'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination)

Private Sub worksheet_calculate()

Dim LastRow As Long

With Sheets("Sheet2")
    ' find last row in Sheet 2
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

'Copy the data and Paste in Sheet 2 (+1 for next avaialble row)
Sheets("Sheet1").Range("I1").Copy Sheets("Sheet2").Range("A" & LastRow + 1)

Application.CutCopyMode = False

End Sub

Edit 1:

Modifed code according to PO updated information:

In order to avoid the F9 infinite loop, use the Application.EnableEvents = False inside your Sub.

Private Sub worksheet_calculate()

'In this example I am Copying the Data from Sheet1 (Source) to Sheet2 (Destination)
Dim LastRow As Long

Application.EnableEvents = False

With Sheets("Sheet2")
    ' find last row in Sheet 2
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row              
    ' copy the values only from "Sheet1" to "Sheet2"
    .Range("A" & LastRow + 1).Value = Sheets("Sheet1").Range("I1").Value
End With

Application.CutCopyMode = False    
Application.EnableEvents = True

End Sub

You can do the Copy>Paste in one line like @user3598756 wrote:

With Worksheets("Sheet2")
   .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("I1").Value
End With

Upvotes: 0

user3598756
user3598756

Reputation: 29421

Should you be interested in pasting values only

Private Sub worksheet_calculate()
   With Worksheets("Sheet2")
       .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = Worksheets("Sheet1").Range("I1").Value
    End With
End Sub

Upvotes: 0

bzimor
bzimor

Reputation: 1628

You chose last used row incorrectly. Try this:

Private Sub worksheet_calculate()
Dim lstrow As Integer
'Method 2
'Copy the data
Sheets(1).Range("I1").Copy
'Activate the destination worksheet
Sheets(2).Activate
'Select the target range
lstrow = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
'Paste in the target destination
ActiveSheet.Cells(lstrow + 1, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Upvotes: 0

Related Questions