Macro - Copy speciffic columns when value is X
Closed
little_ghost
TrowaD
- Posts
- 2
- Registration date
- Tuesday February 26, 2013
- Status
- Member
- Last seen
- March 3, 2013
TrowaD
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Related:
- Macro - Copy speciffic columns when value is X
- Excel macro to delete columns based on header ✓ - Forum - Excel
- Excel Macro help, hiding columns ✓ - Forum - Excel
- Macro to insert column in excel based on criteria ✓ - Forum - Excel
- Excel macro to compare columns in two worksheets ✓ - Forum - Excel
- Macro to insert row based on variable cell value (text) - Forum - Excel
4 replies
TrowaD
Feb 26, 2013 at 10:40 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Feb 26, 2013 at 10:40 AM
Hi Little ghost,
I'm having some trouble checking out your file.
Check out the code below and see if you can work it out.
Run this code from data sheet.
The code will check column W for a "Specific Value".
When found it will copy column D:G of the same row and paste it to another workbook, which is already opened, at the first available row in column A.
Change the necessary information.
Let me know if more assistance is needed.
Best regards,
Trowa
I'm having some trouble checking out your file.
Check out the code below and see if you can work it out.
Sub CopyRows() Dim lRow As Integer lRow = Range("W" & Rows.Count).End(xlUp).Row For Each cell In Range("W2:W" & lRow) If cell.Value = "Specific Value" Then Range(Range("D" & cell.Row), Range("G" & cell.Row)).Copy _ Workbooks("Name of your opened Workbook").Sheets("Destination Sheet").Range("A" & Rows.Count).End(xlUp).Offset(1, 0) End If Next cell End Sub
Run this code from data sheet.
The code will check column W for a "Specific Value".
When found it will copy column D:G of the same row and paste it to another workbook, which is already opened, at the first available row in column A.
Change the necessary information.
Let me know if more assistance is needed.
Best regards,
Trowa
little_ghost
Mar 3, 2013 at 08:25 AM
- Posts
- 2
- Registration date
- Tuesday February 26, 2013
- Status
- Member
- Last seen
- March 3, 2013
Mar 3, 2013 at 08:25 AM
Dear Torwa,
Thank you for your reply and sorry for my late reply.
Strange that you can't access the file I uploaded... what happens when you click the link? Anyway, further down this post, I'll put the code I was using.
Your script works but there are a couple of problems I have with it:
1. It copies a range (D-G) but I need it to copy only a few columns (D, F, H, W, AA) and maybe more columns in the future.
2. The script runs in the workbook that has the data in it yet it should run in the blank (destination) workbook (the workbook with the data in it will not be open)
Eventually the macro will have to run whenever the blank/destination workbook is opened and then get it's inout from the source workbook that is closed.
This is the code I was using:
Keep in mind:
A) it was running on the destination sheet, but still in the same workbook
B) had to be run manually
C) Copied all columns instead of only the ones needed (D, F, H, W, AA)
I hope this is all clear.
Kind regards,
Little_Ghost
Thank you for your reply and sorry for my late reply.
Strange that you can't access the file I uploaded... what happens when you click the link? Anyway, further down this post, I'll put the code I was using.
Your script works but there are a couple of problems I have with it:
1. It copies a range (D-G) but I need it to copy only a few columns (D, F, H, W, AA) and maybe more columns in the future.
2. The script runs in the workbook that has the data in it yet it should run in the blank (destination) workbook (the workbook with the data in it will not be open)
Eventually the macro will have to run whenever the blank/destination workbook is opened and then get it's inout from the source workbook that is closed.
This is the code I was using:
Sub CopyABS() Set Source = Sheets("net-wbs") Set Destination = Sheets("ABS_only") Dim d Dim j d = 1 j = 2 Do Until IsEmpty(Source.Range("W" & j)) If Source.Range("W" & j) = "CS ABS" Then d = d + 1 Destination.Rows(d).Value = Source.Rows(j).Value End If j = j + 1 Loop End Sub
Keep in mind:
A) it was running on the destination sheet, but still in the same workbook
B) had to be run manually
C) Copied all columns instead of only the ones needed (D, F, H, W, AA)
I hope this is all clear.
Kind regards,
Little_Ghost
TrowaD
Mar 4, 2013 at 11:46 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Mar 4, 2013 at 11:46 AM
Hi Little Ghost,
Try this combo:
Put this code in a module:
Then put this code in ThisWorkbook:
Adjust the values with green text behind them.
That should do it.
Best regards,
Trowa
Try this combo:
Put this code in a module:
Sub CopyRows() Dim lRow As Integer Dim dWB As String dWB = "Destibook" 'Name of Destination Workbook Workbooks.Open Filename:="C:\Documents\Sourcebook.xls" 'Full file path of source WB lRow = Sheets("net-wbs").Range("W" & Rows.Count).End(xlUp).Row For Each cell In Sheets("net-wbs").Range("W2:W" & lRow) If cell.Value = "CS ABS" Then Workbooks(dWB).Sheets("ABS_only").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("net-wbs").Range("D" & cell.Row).Value Workbooks(dWB).Sheets("ABS_only").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("net-wbs").Range("F" & cell.Row).Value Workbooks(dWB).Sheets("ABS_only").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("net-wbs").Range("H" & cell.Row).Value Workbooks(dWB).Sheets("ABS_only").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("net-wbs").Range("W" & cell.Row).Value Workbooks(dWB).Sheets("ABS_only").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("net-wbs").Range("AA" & cell.Row).Value End If Next cell Workbooks("Sourcebook.xls").Close 'Name of source WB End Sub
Then put this code in ThisWorkbook:
Private Sub Workbook_Open() Call CopyRows End Sub
Adjust the values with green text behind them.
That should do it.
Best regards,
Trowa
TrowaD
Mar 4, 2013 at 11:53 AM
- Posts
- 2886
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- June 27, 2022
Mar 4, 2013 at 11:53 AM
Oh and to comment on your uploaded file.
I see three sheets in landscape much like pdf.
When I click on the option to download, then I.E. tells that "the contents cannot be shown in a frame". No idea what it means.
I see three sheets in landscape much like pdf.
When I click on the option to download, then I.E. tells that "the contents cannot be shown in a frame". No idea what it means.