Check If Value Is In Closed Worksheet

leighmetcalfe

New Member
Joined
Oct 27, 2012
Messages
39
I currently have the below macro to copy data from a my sales workbook and paste the values to a closed workbook.

I'm struggling to find a macro that can check column C of the closed workbook for "VE01", if this appears then delete the line and run the transfervalues sub

This is to prevent duplicate entries.

Code:
Sub TransferValues()
Application.ScreenUpdating = False
Dim wsMain As Worksheet
Dim wbData As Workbook
Dim wsData As Worksheet
Dim rngToCopy As Range
Dim C As Long
Dim ar As Range
Dim cl As Range


Dim Lastrow As Long
Dim rngDestination As Range


Set wsMain = ThisWorkbook.Sheets("Enquiry Ticket")


Application.DisplayAlerts = False
' you need to open the workbook
Set wbData = Workbooks.Open("T:\Sales\Sales\Test.xlsb")
Set wsData = wbData.Sheets("C&B")
Set rngToCopy = wsMain.Range("A3:AI3")


'Get the last row in Database sheet:
Lastrow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row


C = 1
For Each cl In rngToCopy
    cl.Copy
    wsData.Cells(Lastrow + 1, C).PasteSpecial xlPasteValues
    C = C + 1
Next cl


wbData.Close True '<-- close and save the changes made
Application.DisplayAlerts = True '<-- restore settings
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,705
Office Version
  1. 365
Platform
  1. Windows
Are you trying to delete the row in wsData ?
Try putting this code before copying the data from wsMain.Range("A3:AI3")
Code:
  On Error Resume Next
    With wsData
        .Rows(WorksheetFunction.Match("VE01", .Columns(3), 0)).EntireRow.Delete
    End With
  On Error GoTo 0
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,705
Office Version
  1. 365
Platform
  1. Windows
To get row number before opening the workbook ..
(in all cases if > 0 then that row is the one to delete)

EITHER
1. Put this formula in any cell in the open workbook (value returned is row number)
=IFERROR(MATCH("VE01",'[T:\Sales\Sales\Test.xlsb]C&B'!$C:$C,0),0)

OR
2. Put MATCH formula in the closed workbook and use the archaic ExecuteExcel4Macro to get its value

With formula in cell A1 in Test.xlsb
=IFERROR(MATCH("VE01",C:C,0),0)

Place VBA in open workbook - where r is the ROW number (or 0 if not found)

Get the syntax correct like this (note R1C1 notation - C1 would be R1C3)
Code:
  [COLOR=#ff0000]r[/COLOR] = ExecuteExcel4Macro("'T:\Sales\Sales\[Test.xlsb]C&B'![B]R1C1[/B]")

or call a function that does the job
Code:
  [COLOR=#ff0000]r[/COLOR] = GetValue("T:\Sales\Sales", "Test.xlsb", "C&B", "A1")

Code:
 Function GetValue(path, file, sheet, ref)
    Dim arg As String
[COLOR=#006400][I]'   Make sure the file exists[/I][/COLOR]
    If Right(path, 1) <> "" Then path = path & ""
    If Dir(path & file) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
[COLOR=#006400][I]'   Create the argument[/I][/COLOR]
    arg = "'" & path & "[" & file & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1)
[COLOR=#006400]'[I]   Execute an XLM macro[/I][/COLOR]
    GetValue = ExecuteExcel4Macro(arg)
End Function
credit for function: http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,705
Office Version
  1. 365
Platform
  1. Windows
thanks for your feedback
(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,609
Messages
5,523,883
Members
409,541
Latest member
pgiering

This Week's Hot Topics

Top