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]

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

<tbody>
</tbody>

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]