I am totally stuck and this is probably a very simple fix. I have two sheets "Priority Accounts" and "ACN Dashboard". I need to move data in one column over from 'Priority Accounts" to "ACN Dashboard' if data in column '6' equals x. I have tried auto filter and a few other things but am not getting what I need. I want this to be an automatic thing since this is a live workbook where column 6 in "priority accounts" will be constantly updated. Please help!
I just want to make sure we are not confusing columns with rows here.
Can you confirm that either the data being moved is located in a row or that the x's are located in a row.
One more thing. You want this to be automatic. What did you have in mind:
1) when x is entered, move data.
2) run the code at the end of the day to make sure all data is entered and then remove the x's so they aren't moved twice.
3) run the code at the end of the day to make sure all data is entered and then remove all data being moved.
If you choose for option 1 then please specify move as cut/paste or copy/paste.
Please note that the more column/row references you provide the more accurate the code will be.
Okay so the data being moved is in a row but I only want the information from one cell moved. Example; if Northside Hospital, which is in column A has an "x" in column 6, I want the name "Northside Hospital" to copy to my Sheet 2, which has a running dashboard of all companies that we've engaged with. Sheet 1 is an actua live working document that directors update daily/hourly even so currently there are 300 names in there. Over the next few months, they'll be engaging with every one of them; I want my dashboard on sheet 2 to always have the most up-to-date list of companies engaged.
As far as what option I'd want, I'd want something similar to your option 1; when x is entered, copy data to sheet 2. I know this might complicate things since x's entered yesterday should be excluded from the ones entered today.
One thing you didn't mention is the destination of the copied value on sheet 2. But let me provide a code so you can see how it works.
The code below will place the copied value in the first available cell in column A.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Value = "x" Then
Target.Offset(0, -5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub
Now enter an "x" in column F of sheet 1 and notice that the corresponding value in column A is copied to column A of sheet 2.
Hi Trowa, this is exactly how I foresaw it. I have a few questions though (and keep in mind, i'm a bit of a beginner so might get some words jumbled).
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub
If Target.Value = "x" Then
Target.Offset(0, -5).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
End Sub
My questions are as follows:
Where do I see that whatever is in column A is going to be copied over to Sheet 2?
I have titles to my columns and sheets so maybe this can help not only clarify but teach me at the same time:
Worksheet 1 (where the info is captured) is "priority Accounts"
Column F is "IPS Campaign Engagement"
Column A is "Target Name"
Destinatin:
Sheet 2 is ACN Dashboard
Start at Column B
Row 42 and every row thereafter as needed
Another question: how do I assign it to a button macro once my code is in? The workbook will be updated several times a day so i'm assuming assigning it to a macro would be the best right?
Your first question is unclear to me. Do you want a message box to appear letting you know which value is copied over? Or are you wandering how the code works?
Target.Offset(0, -5).Copy This refers to the value being copied.
Target refers to the value being changed, which is a value in column F.
Offset(0,-5) means 5 columns to the left of column F.
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) This refers to the destination of the copied value. It consists of a sheet reference and a range reference. The range reference looks at the maximum numbers of rows in column A, then moves the cursor up to the first available value and to not overwrite that value we use Offset to move the cursor down a row.
On to your second question.
The macro is setup to run automatically, do you want to run it manually using a shortcut or button?
I've a similar request to the TS, trying to import data to Sheet1 from DB using parameters from Column Y "Status".
If status is WIP then I want to populate Column B to L in Sheet1 from DB using specific columns. I've given the exact columns I want to populate Sheet1 with below:
Sheet1 = DB
Column B = Column C
Column C = Column E
Column D = Column F
Column E = Column K
Column F = Column L
Column G = Column M
Column H = Column N
Column I = Column O
Column J = Column P
Column K = Column Q
Column L = Column AC
Will appreciate your help as I've been trying to solve this using formulas for the longest time.
I've tried the Macro and I realized I forgot to mention that the data in the DB" sheet is exported (copied and pasted) so "WIP" is not manually entered into Column Y under the "DB" sheet.
How then can I adjust the code such that it will still auto populate the fields in Sheet1?
In addition, will it be possible to do a search of Column AD as well for a specific word? E.g. Deskjet 2540.
Therefore the code will only do an auto populate of Sheet1 if the data in Column Y and Column AD of "DB" sheet fulfills both criteria.
And all these bearing in mind that the database is copied and pasted over into DB and not manually entered.
Ok, for that try the code below, which will run automatically when the "Sheet1" sheet is activated.
So put this code under "Sheet1" and not the "DB" sheet as before.
Private Sub Worksheet_Activate()
Dim lRow, lRowDB As Long
Range("B2:L" & Rows.Count).ClearContents
lRowDB = Sheets("DB").Range("Y" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("DB").Range("Y2:Y" & lRowDB)
If cell = "WIP" And cell.Offset(0, 5) = "Deskjet 2540" Then
lRow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
Range("B" & lRow) = Sheets("DB").Range("C" & cell.Row)
Range("C" & lRow) = Sheets("DB").Range("E" & cell.Row)
Range("D" & lRow) = Sheets("DB").Range("F" & cell.Row)
Range("E" & lRow) = Sheets("DB").Range("K" & cell.Row)
Range("F" & lRow) = Sheets("DB").Range("L" & cell.Row)
Range("G" & lRow) = Sheets("DB").Range("M" & cell.Row)
Range("H" & lRow) = Sheets("DB").Range("N" & cell.Row)
Range("I" & lRow) = Sheets("DB").Range("O" & cell.Row)
Range("J" & lRow) = Sheets("DB").Range("P" & cell.Row)
Range("K" & lRow) = Sheets("DB").Range("Q" & cell.Row)
Range("L" & lRow) = Sheets("DB").Range("AC" & cell.Row)
End If
Next cell
End Sub
Now paste your data in the "DB" sheet and see if the desired data shows up in the "Sheet1" sheet.
Put this code under "Sheet1" of the "salesmaster-new" workbook.
Make sure to save your "Tiakeew - DO list Template (Printer)" workbook before selecting "Sheet1" of the "salesmaster-new" workbook or else Excel will ask you if you want to re-open the file without any adjustments being saved.
If the worksheet and/or workbook references above doesn't match yours, then replace them with what you have in the code (best done by using find/replace [Ctrl+ H]).
Private Sub Worksheet_Activate()
Dim lRow, lRowDB As Long
Dim DestBook As Workbook
Set DestBook = Workbooks("salesmaster-new")
Range("B2:L" & Rows.Count).ClearContents
Workbooks.Open Filename:="C:\Users\takyar\Documents\Tiakeew - DO list Template (Printer).xlsx"
Sheets("DB").Select
lRowDB = Sheets("DB").Range("Y" & Rows.Count).End(xlUp).Row
For Each cell In Sheets("DB").Range("Y70:Y" & lRowDB)
If cell = "WIP" And cell.Offset(0, 5) = "Deskjet 2540" Then
lRow = DestBook.Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
DestBook.Sheets("Sheet1").Range("B" & lRow) = Sheets("DB").Range("C" & cell.Row)
DestBook.Sheets("Sheet1").Range("C" & lRow) = Sheets("DB").Range("E" & cell.Row)
DestBook.Sheets("Sheet1").Range("D" & lRow) = Sheets("DB").Range("F" & cell.Row)
DestBook.Sheets("Sheet1").Range("E" & lRow) = Sheets("DB").Range("K" & cell.Row)
DestBook.Sheets("Sheet1").Range("F" & lRow) = Sheets("DB").Range("L" & cell.Row)
DestBook.Sheets("Sheet1").Range("G" & lRow) = Sheets("DB").Range("M" & cell.Row)
DestBook.Sheets("Sheet1").Range("H" & lRow) = Sheets("DB").Range("N" & cell.Row)
DestBook.Sheets("Sheet1").Range("I" & lRow) = Sheets("DB").Range("O" & cell.Row)
DestBook.Sheets("Sheet1").Range("J" & lRow) = Sheets("DB").Range("P" & cell.Row)
DestBook.Sheets("Sheet1").Range("K" & lRow) = Sheets("DB").Range("Q" & cell.Row)
DestBook.Sheets("Sheet1").Range("L" & lRow) = Sheets("DB").Range("AC" & cell.Row)
End If
Next cell
End Sub
