HELP - Extract column specific data

Hi

I was hoping someone could give me some advice. I have attached an example of what I am trying to achieve.

In sheet 1 I have a set of data. I am trying to extract specific columns from that set of data into a new sheet called "EXTRACT". It works as long as I want to extract data into column A of the "Extract" tab. However soon as I want to extract data into column B or C or any other column it does not.

So for example if we look at the code below, if I change the first paste (Sheet1.Paste Destination:=Worksheets("EXTRACT").Cells(erow, 1)) to another column such as (Sheet1.Paste Destination:=Worksheets("EXTRACT").Cells(erow, 2)) it stops working.

I am sure I am missing something simple so if someone could help me it would be appreciated!


Sub Dump()

Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
Sheet1.Cells(i, 1).Copy
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Sheet1.Paste Destination:=Worksheets("EXTRACT").Cells(erow, 1)

Sheet1.Cells(i, 3).Copy
Sheet1.Paste Destination:=Worksheets("EXTRACT").Cells(erow, 9)

Sheet1.Cells(i, 6).Copy
Sheet1.Paste Destination:=Worksheets("EXTRACT").Cells(erow, 10)

Next i
Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range("A1").Select

End Sub

Hi Arkadi


Thank you very much for your help! Much appreciated!

Yeah - it worked great!

Thanks for pointing it out, I was looking at the same code for ages!

Hi

My previous issue is now fixed, but I now have another related problem. When I run the macro through a large set of data, it crashes my workbook.

Therefore do you know of a way that I can modify the code that would help prevent this issue?

I have attached a file with lots of data in which is representative of the amount of data I will have in my excel file. When you press the macro you will see the issue.

Can you help please?

Hi

That worked great!! Many thanks again!

Hi

Thanks for your help previously - the solution you gave has been working well.

However I have tried to modify it slightly by only returning data that meets a certain criteria. However whilst it works it also brings back blank cells (where I presume the data that I do not want to be returned would have been).

Do you know how I can copy only the data that I want (as therefore no blank spaces are copied over) please??

Here is the modified code to what you helped me with below. I have also attached an example worksheet.

Sub Dump()

Dim lastrow As Long, erow As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
erow = Sheet2.Cells(Rows.Count, 8).End(xlUp).Offset(3, 0).Row
For i = 2 To lastrow
    If Sheet1.Range("R" & erow).Value = "Active" Then
        Worksheets("Status Active UIN's").Cells(erow, 1).Value = Sheet1.Cells(i, 7).Value
        Worksheets("Status Active UIN's").Cells(erow, 2).Value = Sheet1.Cells(i, 2)
        Worksheets("Status Active UIN's").Cells(erow, 3).Value = Sheet1.Cells(i, 8)
        Worksheets("Status Active UIN's").Cells(erow, 4).Value = Sheet1.Cells(i, 18)
    End If
    
    erow = erow + 1
Next i

Application.CutCopyMode = False
Sheet2.Columns().AutoFit
Range("A1").Select

Application.ScreenUpdating = False



End Sub

Hi Arkadi

Thanks for your response. It almost works but it does not extract all the data (it has got rid of the blank spaces).

The example I have attached shows it extracts 733 rows of data although when I manually do a filter it shows there are 16765 "active" entries in column R.

Any ideas...maybe I need to change something in the code?

Thanks for your help - its appreciated!!

hmm..I dont think so. I had a quick look over the list and I cannot see any differences in capitalization. If there are, I dont think there is enough to make up the difference between what is rally there and what is being extracted.

Awesome!! Thanks again Arkadi! Works well - you are a champ!


Much appreciated!!