pasting values to first free row in a column
Hi
I have a workbook where I need the values from column A workseet 1 to appear in column A on worksheet 2. I manually enter different values into column A on worksheet 2 so I need the values being copied from worksheet 1 into worksheet 2 to go into the first free row. Also, if a macro is the correct solution, is it possible for the macro to not copy values that have already been copied across?
Sure, put this macro into the sheet module of Sheet1, it will watch column A. Anytime you enter a new value it will add it to sheet2 column A if it is new.
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target If cell.Column = 1 And cell <> "" Then On Error Resume Next If Application.WorksheetFunction.Match(cell.Value, Sheets("Sheet2").Range("A:A"), 0) = 0 Then Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value End If End If Next cell End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
cool, works perfectly, thanks very much
what changes to the code would i need to make to get column b to copy as well as column a? thanks
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target If (cell.Column = 1 Or cell.Column = 2) And cell <> "" Then On Error Resume Next If Application.WorksheetFunction.Match(cell.Value, Sheets("Sheet2").Range("A:A"), 0) = 0 Then Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value End If End If Next cell End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
thanks JBeaucaire but I need column A to go to column A and column B to go to column B... if i added a data validation to column 1, would it change the code a all?
So, to do only columns 1 and 2:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target On Error Resume Next Select Case cell.Column Case 1 'column A If Application.WorksheetFunction.Match(cell.Value, Sheets("Sheet2").Range("A:A"), 0) = 0 Then Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value Case 2 'column B End If If Application.WorksheetFunction.Match(cell.Value, Sheets("Sheet2").Range("B:B"), 0) = 0 Then Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value End If End Select Next cell End Sub
Last edited by JBeaucaire; 02-10-2012 at 09:27 PM. Reason: Missing END IFs
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Running this code I get the following 'Compile error: Case without Select Case' and the 'Case' in 'Case 2' is highlighted...
Corrected the code above, added the missing END IFs... sorry about that.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
thanks, getting close now! the code will only copy a value across from sheet1 to sheet2 once. I use names in one of the columns and they need to appear many times in sheet 2... help is much appreciated
The macro I gave you is intentionally designed to copy each unique value to sheet2. How odd that you would want multiples on sheet2 if you already have multiples on sheet1.....
Anyway, that's significantly easier if we don't have to check anything:
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target Select Case cell.Column Case 1, 2 'column A or B only Sheets("Sheet2").Cells(Rows.Count, cell.Column).End(xlUp).Offset(1).Value = cell.Value End Select Next cell End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
cool, thats what I was after. If I wanted column B to go to say Column D in sheet2, how would I change the code?
go back to the original layout where each "Case" was separate, and change the destination to something specific instead of dynamic...
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target Select Case cell.Column Case 1 'column A Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value Case 2 'column B Sheets("Sheet2").Range("D" & Rows.Count).End(xlUp).Offset(1).Value = cell.Value End Select Next cell End Sub
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks