Greetings!
I am pretty new to vba and attempting to work through this problem as thoroughly as possible so any direction or help with be much appreciated.
I have 3 worksheets that I am currently using:
I am pretty new to vba and attempting to work through this problem as thoroughly as possible so any direction or help with be much appreciated.
I have 3 worksheets that I am currently using:
- Sheet 1= "Table"- Which holds a list of profit centers (List is located in Range L3:L23)
- Sheet 2= "Setup"- Which holds a list of Accounts, Dept for each profit center listed on "Table" Tab and text string identifier either (FB, Retail or Other)
- Sheet 3= "JV"- Which will list each profit center in Column A and the Account, Dept will need to be copied from "setup" tab (Sample data Below)
Code:
Sub CopyOnCondition()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range
Dim pc
Dim nRow As Long
Dim zRow As Long
Set sh1 = Sheets("Table") 'Edit sheet name
Set sh2 = Sheets("JV") 'Edit sheet name
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
With sh1
On Error Resume Next
For Each c In Range("F2:F292")
pc = c.Offset(0, -1).Value
If c.Value = "FB" And pc = "Restaurant 1" Then
c.Offset(, -5).Resize(1, 3).Copy '<-- copy column A with B
' paste values to the first empty row in Column H of sh2
sh2.Cells(sh2.Rows.Count, 8).End(xlUp).Offset(1).PasteSpecial xlValues
End If
Next c
End With
Application.Calculation = xlCalculationAutomatic
End Sub
Last edited by a moderator: