Macro to paste the data in the next blank row

[rdhar3]

Hi,

This is the first time I working on macro and need some help.

I want to copy the data entered in cells a2 and in "D" colum to of sheet 1 to sheet 2 in the "B" colum and if the data is already there in the "B" colum then in C and then in D and so on.

The only challlenge I am having is the data getting overwritten.

can someone help with it...

 

[rdhar3]

m

 

[villy]

Start here:

Sub ins()
Set ws1 = Sheets(1)
Set ws2 = Sheets(2)
Rng = ws2.Range("B1:B20")
RngC = ws2.Range("C1:C20")
lr = ws2.Cells(Rows.Count, 2).End(xlUp).Row
lrC = ws2.Cells(Rows.Count, 3).End(xlUp).Row
lrD = ws2.Cells(Rows.Count, 4).End(xlUp).Row
For Each cell In Range("A1:A20")
With WorksheetFunction.Application
If IsError(.Index(Rng, .Match(cell, Rng, 0), 1)) Then
ws2.Range("B" & lr + 1).Value = cell
lr = lr + 1
ElseIf IsError(.Index(RngC, .Match(cell, RngC, 0), 1)) Then
ws2.Range("C" & lrC + 1).Value = cell
lrC = lrC + 1
Else
ws2.Range("D" & lrD + 1).Value = cell
lrD = lrD + 1
End If
End With
Next
End Sub

 

[rdhar3]

This is not working please find below the script of the recorded macro.Desired result is to paste the values in the "Count Capturing" sheet in the first blank column.

Sub Transaction_Submit()
'
' Transaction_Submit Macro
' This is to record the submission time of an record
'
'
Range("A2").Select
Selection.Copy
Sheets("Count Capturing").Select
Range("B1").Select
ActiveSheet.Paste
Range("B3").Select
Sheets("Login and Logout").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=NOW()"
Range("G2").Select
Selection.Copy
Sheets("Count Capturing").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Working Sheet'!RC:R[26]C[2],3,0)"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B39"), Type:=xlFillDefault
Range("B4:B39").Select
Range("B34").Select
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("B4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Working Sheet'!RC:R[26]C[2],2,0)"
Range("B4:B11").Select
Sheets("Working Sheet").Select
Sheets("Count Capturing").Select
Range("B4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Working Sheet'!C:C[2],3,TRUE)"
Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B39")
Range("B4:B39").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("B4:B39").Select
Selection.Replace What:="#N/A", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("F28").Select
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 1
Range("B5").Select
Sheets("Working Sheet").Select
Range("A2").Select
Selection.ClearContents
Range("B2").Select
Selection.ClearContents
Range("D4:D100").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
ActiveWorkbook.Save
End Sub

 

[rdhar3]

Thank you villy