Macro Question: IF statements between multiple files

DaltonDub

New Member
Joined
Feb 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am creating a macro that does a couple things that should in theory be easy to do, but, nonetheless is far beyond my very basic experience with VBA code.
The objective is to copy parts of a row from file 1 and paste them to Excel file 2 IF the date matches a given date on the second file.
The macro would need to perform an IF statement on every row on file 1.
I have figured out how the copy and pasting portion but need to figure out:
-how to set up the IF statement to reference another file
-how to apply said if statement to every row with data.

I am hoping I can get an example of the VBA code needed, and if not just learn a bit more about how I can accomplish my desired result.

Thanks,

Dalton
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You might be able to work with this. My comments in green explain what the code is doing and what you need to do.

Example:

VBA Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range  'Declare variables for sheets and cell that changes row location.
Set sh1 = Workbooks(1).Sheets(1)  'Here you substitute names enclosed in quotation marks for each (1).
Set sh2 = Workbooks(2).Sheets(1)  'Same thing
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp)) 'Initialize a For Each loop
        'This above statement starts the loop that will walk down the rows, and it inializes the
        'c variable which will be a cell in column A, changing on each iteration of the loop.
        'The statement simultaneously finds the last row with data to for the limit of the loop.
        If c.Value = sh2.Range("A2").Value Then 'this is where you would compare the dates.
            'I used arbitrary locations on the sheets for the dates, but you can change that.
            MsgBox "Dates match"
        Else
            MsgBox "Dates do not match"
        End If
        'The message boxes are fillers in this example.  In actual code you would have a statement
        'that executes an action based on the outcome of the date comparison.
    Next  'This increments the loop to the next iteration until the end of the data in column A.
End Sub
 

DaltonDub

New Member
Joined
Feb 10, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you so much @JLGWhiz!

Unsurprisingly I still don't have it working. I am having trouble figuring out how to copy and paste whichever row the macro is looking.
I am sure there are still lots of issues with the code. But it's exciting to make progress!

VBA Code:
Sub Copy_Paste_IF()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Workbooks("Step 1").Sheets("Sheet1")
Set sh2 = Workbooks("Step 2").Sheets("AP Input")
    For Each c In sh1.Range("Q2", sh1.Cells(Rows.Count, 1).End(xlUp))
        If c.Value = sh2.Range("H6").Value Then
            Workbooks("Step 1.xlsx").Worksheets("Sheet1").Range("J2:Z2").Copy
            Rows(6).Insert
            Workbooks("Step 2.xlsx").Worksheets("AP Input").Range("A5:Q5").PasteSpecial Paste:=xlPasteValues
            MsgBox "Dates match"
        Else
            MsgBox "Dates do not match"
        End If
        IsEmpty (H6)
         Next
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
See my comments in the code.

VBA Code:
Sub Copy_Paste_IF()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Set sh1 = Workbooks("Step 1").Sheets("Sheet1")
Set sh2 = Workbooks("Step 2").Sheets("AP Input")
    For Each c In sh1.Range("Q2", sh1.Cells(Rows.Count, 17).End(xlUp)) 'Unless you want to check each cell in columns A:Q use the same column reference in both parameters.
        If c.Value = sh2.Range("H6").Value Then
            sh1.Range("J2:Z2").Copy 'Use your variables to shorten code
            Rows(6).Insert
            If sh2.Range("A5") = "" Then
                sh2.Range("A5").PasteSpecial Paste:=xlPasteValues  'You only need the anchor cell
            Else
                sh2.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues 'If row 5 is populated get next available row.
            MsgBox "Dates match"
        Else
            MsgBox "Dates do not match" 'Do you really need this?  If no copy/paste there is no match.
        End If
        'IsEmpty (H6) This could be deleted, it is useless.
   Next
End Sub
 

Forum statistics

Threads
1,144,394
Messages
5,724,084
Members
422,536
Latest member
Zeeshan53

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top