CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
acr
acr

Reputation: 1746

copy entire column of an excel to another depends upon change in a cell value

My requirement is whenever there is an update to my main workbook's (main.xlsm)sheet1 "A1" cell, I need to copy that entire row of data into a different excel(say working.xlsm). The main sheet has columns from A to M and data capture should occur depends upon A1 cell data change. All data get preserved in the second excel and all new data will be added to next row.

I have added below code to my second workbook(working.xlsm), where I need to save all the data, but it's not working. I am not sure how to use a range with with function 

Private Sub Worksheet_Calculate()

With Sheet1
    With .Cells(.Rows.Count, "B").End(xlUp)
        If .Value <> Workbooks("main.xlsm").Worksheets("Sheet1").Range("A1").Val‌ue  Then
            .Offset(1).Value = Sheet1.Range("A:M").Value
        End If
    End With
End With

End Sub

Upvotes: 1

Views: 494

Answers (1)

barvobot
barvobot

Reputation: 897

This code will, if put in the sheet on main.xlsm, copy A1:M1 of main.xlsm to the first blank row of working.xlsm's Sheet1 whenever A1 of the sheet is changed. This assumes that working.xlsm is currently open as well, for reference.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Set ws = Workbooks("working").Sheets("Sheet1")
If Not Intersect(Target, Range("A1")) Is Nothing Then 
    Range("A1:M1").Copy 
    ws.Range("A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
End If
End Sub

Is this what you were looking for?

Upvotes: 1

Related Questions