VBA - Comparing data in two sheets where comparison sheet is referenced in Cell of sheet 1

[staylor88]

Hi Guys,

First time poster here - thanks for having me.

I have searched a few forums and watched some video tutorials but sadly this just isnt "clicking" in my head. I wondered if one of you could assist me please.

Requirements:

I have 2 Workbooks - WB1 and WB2, WB1 sends data to WB2, currently i have no checks for previous data loads so i can duplicate data if im not vigilant.

WB1 - is a master file that pulls data from various sources and then uploads to WB2.

WB1 Column i need to compare is the date column in Sheet "Data" in Column A to see if the days data has already been loaded.

The location of WB2 is referenced in Cell B2 on a sheet called "File" in WB1. ( C:\Users\ST20329\Desktop\Test Book 2.xlsx )

WB2 receives the data on Sheet "Data" with the date still being in Column A ( data layout does not change between the books)

I need to know how to right a code that opens WB2 from the referenced location, then compares Column A of both Books Data sheets to see if Date X is already present.

If the date is present i need a pop up box that states "Data Loaded Stop" else continue with the upload macro.

I should note i already have a Macro on WB 1 that sends the data to WB2, so i need this find/compare feature to be an IF so it can continue with macro if data not found.

I'm hoping ive given enough information but please let me know if you require anything else.

Thanks in advance for all and any assistance

Steve

 

[mumps]

Do you want to send data to WB2 only if the date in column A in WB1 is not already present in WB2? If so, your current macro could be modified to do exactly that. If this is the case, could you post your current macro?

 

[staylor88]

Do you want to send data to WB2 only if the date in column A in WB1 is not already present in WB2? If so, your current macro could be modified to do exactly that. If this is the case, could you post your current macro?

Hi There Mumps,

Thats exactly it yes, so if the date is NOT present in WB2 i want to send the data. if it IS present i would like a MSGbox saying stop.

WB1 and WB2 and the cell locations referenced in original post are an example - i would need to modify the code to work with my actual books but due to DPA i cant share the data.

The current Macro below was not wrote by me but a previous employee. I am relatively new to VBA so i don't fully understand how the below works.

My understanding though is that it pulls data from one file then splits it by account and pushes to relevant account sheets on WB2.

Some sheets/accounts dont always have data for a particular day.

So i have added a new sheet to WB2 that has a list of accounts and the max date from each sheet. Then taken a max date from that list - so i would compare the date from WB1 to that Max date.


Sub AlternateUpload()

Application.ScreenUpdating = False

Dim TW As Workbook
Dim AW As Workbook
Dim WS As Worksheet
Dim DVC
Dim DDate As Range ' DDate = Despatch date in DVC File
Dim DC




Workbooks.Open Range("DVC"), UpdateLinks:=False, writerespassword:="Geoffrey", ignorereadonlyrecommended:=True


Set TW = ThisWorkbook
Set AW = ActiveWorkbook
DVC = ThisWorkbook.Sheets("Validation").Range("AQ18")


TW.Activate


For Each WS In TW.Worksheets
If Right(WS.Name, 3) = " DL" Then

WS.Activate
If WS.Cells(2, 1) = "" Then 'if there is nothing to export then skip this tab

Else
'Export

Lrow = Application.WorksheetFunction.Count(Range(Cells(2, 1), Cells(1048576, 1))) + 1
awws = WS.Name
Range(Cells(2, 22), Cells(Lrow, 29)).Formula = Range(Cells(2, 22), Cells(2, 29)).Formula
Range(Cells(2, 1), Cells(Lrow, 29)).Copy
Workbooks(DVC).Sheets(awws).Activate
Cells(1048576, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

End If

End If


Next



TW.Activate
TW.Sheets("Main Page").Select

ActiveSheet.Shapes("Rounded Rectangle 6").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(192, 192, 192)

AW.Activate
AW.Sheets("Summary").Select


Application.ScreenUpdating = True

Set TW = Nothing
Set AW = Nothing

End Sub

 

[staylor88]

Do you want to send data to WB2 only if the date in column A in WB1 is not already present in WB2? If so, your current macro could be modified to do exactly that. If this is the case, could you post your current macro?

Further to the above Mumps,

Simply because im a bit of a noob haha

Id like the below to happen if this makes sense ( See bold comments)

Sub AlternateUpload()

Application.ScreenUpdating = False

Dim TW As Workbook
Dim AW As Workbook
Dim WS As Worksheet
Dim DVC


Workbooks.Open Range("DVC"), UpdateLinks:=False, writerespassword:="Geoffrey", ignorereadonlyrecommended:=True


Set TW = ThisWorkbook
Set AW = ActiveWorkbook
DVC = ThisWorkbook.Sheets("Validation").Range("AQ18")


TW.Activate


New code to check dates present or not.
If present show mssg box

else ( do the below )

For Each WS In TW.Worksheets
If Right(WS.Name, 3) = " DL" Then

WS.Activate
If WS.Cells(2, 1) = "" Then 'if there is nothing to export then skip this tab

Else
'Export

Lrow = Application.WorksheetFunction.Count(Range(Cells(2, 1), Cells(1048576, 1))) + 1
awws = WS.Name
Range(Cells(2, 22), Cells(Lrow, 29)).Formula = Range(Cells(2, 22), Cells(2, 29)).Formula
Range(Cells(2, 1), Cells(Lrow, 29)).Copy
Workbooks(DVC).Sheets(awws).Activate
Cells(1048576, 1).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

End If

End If


Next



TW.Activate
TW.Sheets("Main Page").Select

ActiveSheet.Shapes("Rounded Rectangle 6").Select
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(192, 192, 192)

AW.Activate
AW.Sheets("Summary").Select


Application.ScreenUpdating = True

Set TW = Nothing
Set AW = Nothing

End Sub

 

[mumps]

It is hard to decipher what the macro does without seeing the actual data. Would it be possible to do a manual mockup of each workbook using generic data to protect confidentiality? I would only need a dozen or so rows of data to give me an idea of what it looks like. The generic data would have to be organized in exactly the same way as the actual data. Then you can use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets. Alternately, you could upload a copy of your two generic files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

 

[staylor88]

It is hard to decipher what the macro does without seeing the actual data. Would it be possible to do a manual mockup of each workbook using generic data to protect confidentiality? I would only need a dozen or so rows of data to give me an idea of what it looks like. The generic data would have to be organized in exactly the same way as the actual data. Then you can use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your two sheets. Alternately, you could upload a copy of your two generic files to a free site such as www.box.com or www.dropbox.com. Once you do that, mark each file for 'Sharing' and you will be given a link to each file that you can post here.

i appreciate that its hard to decipher - let me throw something together for us - thankyou