I have a monthly data of 178 rows and 7 columns. I am trying to add monthly data to big database by macro. I need to prevent duplicates. My macro is like following until now. I could remove duplicates but I cannot figure out how to prevent. I need to prevent exactly matching rows. Do you have any idea?
Thanks
Sub Import_Data()
'
' Import_Data Macro
'
'
ChDir "C:\Users\HKNur\Documents\VBA AHG\VBA"
fox = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Workbooks.OpenText Filename:= _
fox, Origin:=437, _
StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:= _
True
ActiveSheet.Move Before:=Workbooks("Monthly Update.xlsm").Sheets(1)
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
Sub fill_blanks()
'
' fill_blanks Macro
'
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
End Sub
Sub Add_Date()
'
' Add_Date Macro
'
'
donkey = Left(ActiveSheet.Name, 7)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
ActiveCell.FormulaR1C1 = donkey
Range("A3").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub
Sub Append_Data()
'
' Append_Data Macro
'
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Users\HKNur\Documents\VBA AHG\VBA\Orders.xlsx"
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Selection.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7), Header:=xlNo
ActiveWindow.Close savechanges:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
Thanks
Sub Import_Data()
'
' Import_Data Macro
'
'
ChDir "C:\Users\HKNur\Documents\VBA AHG\VBA"
fox = Application.GetOpenFilename("Text Files (*.txt), *.txt")
Workbooks.OpenText Filename:= _
fox, Origin:=437, _
StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(11 _
, 1), Array(23, 1), Array(28, 1), Array(43, 1), Array(53, 1)), TrailingMinusNumbers:= _
True
ActiveSheet.Move Before:=Workbooks("Monthly Update.xlsm").Sheets(1)
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub
Sub fill_blanks()
'
' fill_blanks Macro
'
'
Range("A1").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Range("A1").Select
End Sub
Sub Add_Date()
'
' Add_Date Macro
'
'
donkey = Left(ActiveSheet.Name, 7)
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select
ActiveCell.FormulaR1C1 = "Date"
Range("A2").Select
ActiveCell.FormulaR1C1 = donkey
Range("A3").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
End Sub
Sub Append_Data()
'
' Append_Data Macro
'
'
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.CurrentRegion.Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Users\HKNur\Documents\VBA AHG\VBA\Orders.xlsx"
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
' Selection.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, _
7), Header:=xlNo
ActiveWindow.Close savechanges:=True
Application.CutCopyMode = False
Range("A1").Select
End Sub
Last edited by a moderator: