VBa Copy Selected Columns & Paste To New Workbook

[ir121973]

Hi, I wonder whether someone could help me please.

I'm using the code below to find a cell value and then copy and paste into "Sheet 1".

Dim Src As Worksheet, Dst As Worksheet
Dim LastRow As Long, r As Range
Dim CopyRange As Range
'Change these to the correct sheet names
Set Src = Sheets("Amalgamation of Search")
Set Dst = Sheets("Sheet1")
LastRow = Src.Cells(Cells.Rows.Count, "C").End(xlUp).Row
For Each r In Src.Range("C2:C" & LastRow)
     If r.Value = "SC" Then
     If CopyRange Is Nothing Then
                Set CopyRange = r.EntireRow
        Else
                Set CopyRange = Union(CopyRange, r.EntireRow)
        End If
     End If
Next r
If Not CopyRange Is Nothing Then
CopyRange.Copy Dst.Range("A1")
End If
End Sub

The problem I'm having is that I'd like to amend this, so that rather than having the need to have a "Sheet1" in my source sheet, I'd like to open a new workbook and paste the data into there.

In addition I'd like to only copy columns A, C and D.

I've spent over a day on this now, and although I've tried various combinations of code I still can't get this to work.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may be able to achieve this.

Many thanks and kind regards

Chris

 

[mumps]

Try:

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim Src As Worksheet
    Dim LastRow As Long
    Set Src = ThisWorkbook.Sheets("Amalgamation of Search")
    LastRow = Src.Cells(Cells.Rows.Count, "C").End(xlUp).Row
    Src.Range("C1:C" & LastRow).AutoFilter Field:=1, Criteria1:="SC"
    Src.Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible).Copy Workbooks.Add.Sheets("Sheet1").Range("A1")
    Src.Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet1").Range("B1")
    Src.Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet1").Range("C1")
    Src.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

After you run the macro, you can save the new workbook with a name of your choosing.

 

[Kenneth Hobson]

Some clarification is needed. If a value in C2 to end of data in column C = "SC" then copy only that row's column's A, C, and D? Is copy needed or just getting the value is needed? Where would the data go? Into a new workbook's Sheet1's first empty row in column A, to it's columns A, C, and D?

There are 2 methods besides your iteration method that would work. The only problem with your iteration is speed. Other than that, it would work fine. Method 2 would be a FindAll() routine (FindAll VBA Function). Method 3 would be a Filter method as Mumps just showed.

 

[ir121973]

Hi @mumps, that's fantastic, it work great.

Sincere thanks for all your help.

Kind Regards

Chris

 

[ir121973]

Hi @Kenneth Hobson,

Many thanks for taking the time to reply to my post, but as @mumps has provided a working solution, please don't spend any time on this.

Regards

Chris

 

[mumps]

You are very welcome.