Related:
- Macro to copy data from one sheet to another based on criteria
- Excel copy data from one sheet to another if a condition is met - Best answers
- Excel formula to copy data if condition met - Best answers
- Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
- Excel macro to copy data from one sheet to another based on criteria ✓ - Forum - Excel
- Excel Macro's copy data from one sheet to another ✓ - Forum - Excel
- Excel - A Macro to populate data based on criteria - How-To - Excel
- Copy Rows from one sheet to others based on a condition ✓ - Forum - Office Software
10 replies
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
Hi Sam,
No worries man, a macro can be written for this.
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.
Best regards,
Trowa
No worries man, a macro can be written for this.
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.
Best regards,
Trowa
katcrisjul
- Posts
- 3
- Registration date
- Monday June 9, 2014
- Status
- Member
- Last seen
- June 12, 2014
Thanks for responding Trowa!
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.
Let me know if I need to further clarify.
Thanks.
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.
Let me know if I need to further clarify.
Thanks.
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
OK Sam, that is more clear.
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.
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.
Is this how you foresaw it?
Best regards,
Trowa
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.
Is this how you foresaw it?
Best regards,
Trowa
katcrisjul
- Posts
- 3
- Registration date
- Monday June 9, 2014
- Status
- Member
- Last seen
- June 12, 2014
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?
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?
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
Hi Sam,
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?
Best regards,
Trowa
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?
Best regards,
Trowa
tiakeew
- Posts
- 4
- Registration date
- Monday September 8, 2014
- Status
- Member
- Last seen
- September 17, 2014
Hi Trowa,
I've been reading this thread for a while and I am not too sure how this works.
The file i'm trying to work can be found here:
https://drive.google.com/file/d/0B8e2lEDjU_14Mkt1VFZaQWg5UlU/edit?usp=sharing
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 been reading this thread for a while and I am not too sure how this works.
The file i'm trying to work can be found here:
https://drive.google.com/file/d/0B8e2lEDjU_14Mkt1VFZaQWg5UlU/edit?usp=sharing
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.
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
Hi Tiakeew,
See if the code below is to your liking.
The code will run automatically when "WIP" is entered in Column Y of the "DB" sheet, after implementing of course.
To implement, right-click on the "DB" sheet tab and select "View code". Paste the code in the big white field that appears.
Here is the code:
Best regards,
Trowa
See if the code below is to your liking.
The code will run automatically when "WIP" is entered in Column Y of the "DB" sheet, after implementing of course.
To implement, right-click on the "DB" sheet tab and select "View code". Paste the code in the big white field that appears.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
If Intersect(Target, Range("Y:Y")) Is Nothing Then Exit Sub
If Target = "WIP" Then
lRow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("Sheet1").Range("B" & lRow) = Range("C" & Target.Row)
Sheets("Sheet1").Range("C" & lRow) = Range("E" & Target.Row)
Sheets("Sheet1").Range("D" & lRow) = Range("F" & Target.Row)
Sheets("Sheet1").Range("E" & lRow) = Range("K" & Target.Row)
Sheets("Sheet1").Range("F" & lRow) = Range("L" & Target.Row)
Sheets("Sheet1").Range("G" & lRow) = Range("M" & Target.Row)
Sheets("Sheet1").Range("H" & lRow) = Range("N" & Target.Row)
Sheets("Sheet1").Range("I" & lRow) = Range("O" & Target.Row)
Sheets("Sheet1").Range("J" & lRow) = Range("P" & Target.Row)
Sheets("Sheet1").Range("K" & lRow) = Range("Q" & Target.Row)
Sheets("Sheet1").Range("L" & lRow) = Range("AC" & Target.Row)
End If
End Sub
Best regards,
Trowa
tiakeew
- Posts
- 4
- Registration date
- Monday September 8, 2014
- Status
- Member
- Last seen
- September 17, 2014
Hi Trowa,
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.
Thanks for your help!
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.
Thanks for your help!
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
Hi Tiakeew,
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.
Now paste your data in the "DB" sheet and see if the desired data shows up in the "Sheet1" sheet.
Best regards,
Trowa
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.
Best regards,
Trowa
tiakeew
- Posts
- 4
- Registration date
- Monday September 8, 2014
- Status
- Member
- Last seen
- September 17, 2014
Hi Trowa,
How can I edit the code to copy the values from the Worksheet "DB" to another Workbook?
Been looking at this code here:
Sub mySales()
Dim LastRow As Integer, I As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRow
If Cells(i, 1) = Date And Cells(i, 2) = "Sales" Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\takyar\Documents\salesmaster-new.xlsx"
Worksheets("Sheet1?).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
But it does not fit what I want in terms of copying the data from selected columns.
Would appreciate if you could help!!
Thank you
How can I edit the code to copy the values from the Worksheet "DB" to another Workbook?
Been looking at this code here:
Sub mySales()
Dim LastRow As Integer, I As Integer, erow As Integer
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LastRow
If Cells(i, 1) = Date And Cells(i, 2) = "Sales" Then
Range(Cells(i, 1), Cells(i, 7)).Select
Selection.Copy
Workbooks.Open Filename:="C:\Users\takyar\Documents\salesmaster-new.xlsx"
Worksheets("Sheet1?).Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i
End Sub
But it does not fit what I want in terms of copying the data from selected columns.
Would appreciate if you could help!!
Thank you
TrowaD
- Posts
- 2674
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- October 27, 2020
Hi Tiakeew,
Here is my code with adjustments.
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]).
Best regards,
Trowa
Here is my code with adjustments.
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
Best regards,
Trowa
