VBA Copy and Paste

[Zdavis123]

Hi there. Hoping you might be able to guide me in the right direction, been looking online and I'm not having any luck.
When data is entered in one worksheet im wanting to copy and paste that into another worksheet in the same cell references.
Eg where data is entered into worksheet1 in b5, to also populate it into b5 in worksheet2, and then b6 into b6, ect.
Can use easily reference the cells in worksheet2 to 1 but its slowing down my workbook. Could this be done easily in vba?
Any ideas? Ty!

 

[Joe4]

Welcome to the Board!

Yes, this can be done easily using VBA, assuming the data is being entered in manually and not the result of formulas or other outside links.
Do you want it to do this for the ENTIRE worksheet, or just specific columns and rows?"
If for specific columns and rows, let us know which ones.

 

[Zdavis123]

Hi Joe, yeah for all data entered manually into the sheet1 from b5 downwards into sheet2 b5 and then b6, b7, ect
What im hoping to do is then sort the dataset in sheet 2 based on descending order in column H based on a value identified from index/match.
I have the code to sort it and it works perfectly but getting the data from sheet 1 to 2 is a pain haha

 

[Joe4]

On Sheet1, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   See if update in column B after row 4
    If Target.Column = 2 And Target.Row > 4 Then
        Sheets("Sheet2").Range(Target.Address).Value = Target.Value
    End If

End Sub

It will now automatically write the values from column B below row to Sheet2, as they are entered, as long as one cell is being updated at a time (i.e. not using Copy/Paste to update many cells at once).

 

[Zdavis123]

You're a bloody legend thank you!

 

[Joe4]

You are welcome.
Glad I was able to help!