Copy to Another

[billandrew]

Looking to copy 6 columns which are not adjacent to each other, if cell value = a specific criteria to another Workbook starting in Column K.
The column row height could change when received.

The order on the copied "Master" sheet is
- Column B
- Column C
- Column I
- Column J
- Column E
- Column F


Hope this provides the necessary info.

 

[mumps]

What is the specific criteria? In which column is the criteria? What is the destination sheet name? What is the destination workbook name including the extension (xls, xlsx, xlsm)?

 

[billandrew]

The data is in the colums stated in Post 1. The destination workbook is "Daily Report". The file extension of the destination workbook is .xlsx
The file extension of the data to be copied is csv.

Thank you

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[billandrew]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]Product[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Region [/TD]
[TD]Customer[/TD]
[TD]Qty [/TD]
[TD]Rev[/TD]
[TD]Cog[/TD]
[TD]ID[/TD]
[TD]Profit[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40084[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]A292[/TD]
[TD]Central[/TD]
[TD]MJewelry[/TD]
[TD]400[/TD]
[TD]8517[/TD]
[TD]4654[/TD]
[TD]1001[/TD]
[TD]3953[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]39897[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]B722[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]West[/TD]
[TD]AGlass[/TD]
[TD]940[/TD]
[TD]23144[/TD]
[TD="align: -webkit-right"]11703[/TD]
[TD]1002[/TD]
[TD]11485[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40072[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]E438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Central[/TD]
[TD]RSafety Company[/TD]
[TD]190[/TD]
[TD]5520[/TD]
[TD]2958[/TD]
[TD]1003[/TD]
[TD]2562[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40098[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]E438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]East[/TD]
[TD]BPlastic[/TD]
[TD]130[/TD]
[TD]3933[/TD]
[TD]2024[/TD]
[TD]1004[/TD]
[TD]1909[/TD]
[TD]W[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]39941[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]C409[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]West[/TD]
[TD]MJewelry[/TD]
[TD]440[/TD]
[TD]11304[/TD]
[TD]5936[/TD]
[TD]1005
[/TD]
[TD]5368[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40048[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]E438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]North[/TD]
[TD]MJewelry[/TD]
[TD]570[/TD]
[TD]17584[/TD]
[TD]8875[/TD]
[TD]1006[/TD]
[TD]8709[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40056[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]D625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]East[/TD]
[TD]BPlastic[/TD]
[TD]380[/TD]
[TD]10196[/TD]
[TD]5521[/TD]
[TD]1007[/TD]
[TD]4675[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40039[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]D625[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]North[/TD]
[TD]Ace Contruction[/TD]
[TD]690[/TD]
[TD]18322[/TD]
[TD]10026[/TD]
[TD]1008[/TD]
[TD]8296[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40133[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]E438[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Central[/TD]
[TD]MJewelry[/TD]
[TD]580[/TD]
[TD]16850[/TD]
[TD]9031[/TD]
[TD]1009[/TD]
[TD]7819[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]40101[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]A292[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]West[/TD]
[TD]Marks Roofing[/TD]
[TD]550[/TD]
[TD]11228[/TD]
[TD]6276[/TD]
[TD]1010[/TD]
[TD]4952[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD="width: 90, align: right"]39964[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119"]C409[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]East[/TD]
[TD]Good Traders[/TD]
[TD]910[/TD]
[TD]23143[/TD]
[TD]12276[/TD]
[TD]1011[/TD]
[TD]10867[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]


Above is a small sample of the data.

if column C = North or Central copy Column B ,C, I, J, E & F to Workbook 2 Sheet1.

 

[mumps]

Make sure that both workbooks are open and that the "Master" sheet in the source workbook is the active sheet. Place this macro in a regular module in the source workbook and run it from there.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:J" & bottomA).AutoFilter Field:=3, Criteria1:="=Central", Operator:=xlOr, Criteria2:="=North"
    Intersect(Rows("2:" & bottomA), Range("B:B,C:C,I:I,J:J,E:E,F:F")).Copy Workbooks("Daily Report.xlsx").Sheets("Sheet1").Cells(Rows.Count, "K").End(xlUp).Offset(1, 0)
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[billandrew]

Wow

Mumps, I am unable to try this at the moment, however I gave you so little and very disorganized description of what I needed and you continued with the above. I can't say enough Thanks....

and I'm sure it will work.

 

[mumps]

Glad to help. Please let me know if it doesn't work out.

 

[billandrew]

The Filter and copy work perfect, however the order in which I need the columns to be pasted in the Daily Report are incorrect. If possible I need the Order to be

Column B, Column C, Column I Column J, Column E, Column F. Currently is placing the copied Columns in their order on the Start Worksheet.

 

[billandrew]

Working on another method (see below) however this does the same as the code provided and is not as fast or efficient.

I found a workaround, not too sure I like it though. I use the first 4 columns ( B, C, I & J) in one module, run the code, then in a second module use the remaining Columns I want adjacent to Column J which is Columns D & E. This works however I think there is a better way.

Thanks for any help on this...

[Sub CopyColData2()
        Dim Finalrow As Long, lastRow As Long, i As Long
        Dim Rng2Copy As Range
        Dim Ws As Worksheet, Ws2 As Worksheet
        Set Ws = Workbooks("Master.csv").Worksheets("Sheet1")
        Set Ws2 = Workbooks("Daily Report.xlsx").Worksheets("Sheet1")
        Application.ScreenUpdating = True
        Ws.Activate
        With Ws
        lastRow = .Cells(.Rows.Count, 3).End(xlUp).Row
        For i = 2 To lastRow
        If Trim(.Cells(i, "C").Value) = "Central" Or Trim(.Cells(i, "C").Value) = "North" Then
        Set Rng2Copy = Application.Union(.Range("B" & i), .Range("C" & i), .Range("I" & i), .Range("J" & i), .Range("E" & i), .Range("F" & i))
            Rng2Copy.Copy
            
            Finalrow = Ws2.Cells(.Rows.Count, "K").End(xlUp).Offset(1, 0).Row
            Ws2.Range("K" & Finalrow).PasteSpecial xlPasteAll
            End If
            Next i
            End With      
 Application.ScreenUpdating = False
        End Sub/CODE]