Copy rows from one sheet to another based on a list

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
Hi

I am trying to create a macro which would copy the entire row of data from one sheet based on the a list of values in another sheet, additionally it should remove any rows which might have the value of not required. The sheet 1 will have the below values :-

abb
bcd

<tbody>
</tbody>
The sheet 2 would be having having data in the following format :-

NameStatuslocation
abbNot Requirednew york
abbfrance
bcdfrance
bcdfrance
fghfrance
tyfrance
hugermany
tyNot Requiredgermany
fghgermany
opgermany
ergermany

<tbody>
</tbody>
The final data would look something like below

abb <space> France
bcd <space> France
bcd <space> France


Have been able to use something like below :-

Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("CMDB").UsedRange.Rows.Count
J = Worksheets("Sheet2").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet2").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("CMDB").Range("A1:A" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "Done" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet2").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

however not able to replace "done" with the list, please help.

Thanks</space></space></space>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Hi, could you please answer the following?
1) What is the sheet name & data range for the filter values?
2) What is the sheet name & data range for the values to be copied?
3) What is the sheet name where you want the data to be copied 2?
4) Do you want the sheet in point 3 cleared each time?
 

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
Hi, could you please answer the following?
1) What is the sheet name & data range for the filter values?
2) What is the sheet name & data range for the values to be copied?
3) What is the sheet name where you want the data to be copied 2?
4) Do you want the sheet in point 3 cleared each time?
Hello Fluff,

Thanks for your reply, my replies

1) What is the sheet name & data range for the filter values? - we can call it sheet 1, data range is single column
2) What is the sheet name & data range for the values to be copied? the Sheet name is called sheet 2, range would be columns A:C, therefore each row against the value
3) What is the sheet name where you want the data to be copied 2? this can be called sheet 3 starting from Cell A2 - C2
4) Do you want the sheet in point 3 cleared each time? nope just copy.

Thanks again for the reply
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
How about
Code:
Sub CopyFltr()

    Dim Ary As Variant
    Dim UsdRws As Long
    
    Ary = Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    With Sheets("Sheet2")
        If .AutoFilterMode Then .AutoFilterMode = False
        UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1").AutoFilter 1, Ary, xlFilterValues
        .Range("A1").AutoFilter 2, ""
        .Range("A2:C" & UsdRws).SpecialCells(xlVisible).copy Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
    End With
    
End Sub
 

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
How about
Code:
Sub CopyFltr()

    Dim Ary As Variant
    Dim UsdRws As Long
    
    Ary = Application.Transpose(Sheets("Sheet1").Range("A1").CurrentRegion)
    With Sheets("Sheet2")
        If .AutoFilterMode Then .AutoFilterMode = False
        UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
        .Range("A1").AutoFilter 1, Ary, xlFilterValues
        .Range("A1").AutoFilter 2, ""
        .Range("A2:C" & UsdRws).SpecialCells(xlVisible).copy Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .AutoFilterMode = False
    End With
    
End Sub
Hi

getting an error on line <.Range("A2:C" & UsdRws).SpecialCells(xlVisible).Copy Sheets("sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)>

Is there any way i can the worksheet here with the macro till now? Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
When you get the error, are there any visible cells on sheet2, with the exception of the header row?

Is there any way i can the worksheet here with the macro till now? Thanks
I'm afraid I don't understand what you mean by this.
 

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
Its giving an error no cells were found, the macro i am using now.

Sub MoveRowBasedOnCellValue()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Sheet2").UsedRange.Rows.Count
J = Worksheets("Sheet3").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("Sheet3").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Sheet2").Range("A1:A" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "MNO" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet3").Range("A" & J + 1)
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub

hope this helps
 

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
No, the cells are getting filtered with no visible cells. Sorry i meant is there any way i can upload the worksheet here with the macro. Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,317
Office Version
365
Platform
Windows
Have you actually tried the macro I supplied in post#4?

You cannot upload a file to this site. But you can upload to OneDrive or dropbox. mark it as shared 7 then paste a link here.
 

ajilthomas

New Member
Joined
Oct 5, 2011
Messages
8
Have you actually tried the macro I supplied in post#4?

You cannot upload a file to this site. But you can upload to OneDrive or dropbox. mark it as shared 7 then paste a link here.
Hi Fluff,

yes, i did check with the code you send it then gives the error, anyway i have now uploaded the original excel and macro and the new one with your macro. Hope this is easier.

https://1drv.ms/f/s!AkEflwWNrzmfeLIH0JmCdd9PWXw
 

Forum statistics

Threads
1,082,323
Messages
5,364,586
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top