0

I have VBA code which is working but not complete(I can append new rows to another workbook but I cant figure out how to overwrite the cells if they already exist on the other workbook).The logic is if data in column A,E and F already exist on my master workbook then overwrite else append. The data range is A1:F15 Headers are on Row 1 and users will only enter in cells B2:D15, SheetName called TaskList. this is my current VBA and its the third part of my vba I cant workout.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 Then Exit Sub  ' IF ITS A HEADER, DO NOTHING.
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
ErrHandler:
   Application.EnableEvents = True
  Application.ScreenUpdating = True

this captures if someone enters anything in any of the 3 fields and puts their username and entered date in
 If Target.Column = 2 And Target.Row > 1 Or Target.Column = 3 And Target.Row > 1 Or Target.Column = 4 And Target.Row > 1 Then
        Cells(Target.Row, 5).Value = Application.UserName
        Cells(Target.Row, 6).Value = DateTime.Now
  End If
End Sub

This Workbook VBA

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call CopyTasks
End Sub


 Private Sub Workbook_Open()
Dim rng As Range
Set rng = Range("B2:F15")
rng.ClearContents
End Sub

This sends the Data to anotherworkbook

    Sub CopyTasks()
Dim LastRow As Long, i As Long, erow As Long
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Cells(i, 5).Value = Application.UserName Then
Dim rng As Range
Set rng = Range(Cells(i, 1), Cells(i, 6))
rng.Copy
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks.Open("C:\Users\myname\Documents\Master TaskList Data.xlsm")
Set ws = Worksheets("Data")
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
 ActiveSheet.Paste
 ActiveWorkbook.Save
 ActiveWorkbook.Close
 Application.CutCopyMode = False
 End If
Next i
End Sub
New2Programming
  • 351
  • 1
  • 4
  • 17
  • 1
    ① Don't declare row counting variables as `Integer` Excel has more rows than `Integer` can handle. You must use `Long` instead. I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. ②I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this technique to your code (this will probably already fix some issues), then [edit] your question and update the code and include where you get errors and which. – Pᴇʜ Dec 04 '18 at 09:54
  • Thanks I have made a small edit but I still don't know how I would do the overwrite if it already exists. I think I need to do find/lookup on the Master workbook to see if it exists and overwrite if true else append to lastrow? but not sure how to do this. sorry – New2Programming Dec 04 '18 at 11:39

0 Answers0