PDA

View Full Version : Solved: To copy part of the table data



slamet Harto
05-08-2008, 03:50 AM
Dear master,

Need your help on how to split the data from the table above according to the criteria.

For example:
If at column E "Title4" the data is not blank, then macro will copy the table from A4 to J? depending on the content of data. The data will be copied to sheet "Result Expected-1"
While data no.2 (on column E "title4") which have no data on it, only the title and row 6 will be copied, starting from column A, B, C and then K to AC
So column D to J will not be copied at all.

The result is as in sheet "Result expected-1" and "result expected 2"

As usual, many thanks in advance
Rgds, Harto

Simon Lloyd
05-08-2008, 09:10 AM
If at column E "Title4" the data is not blank, then macro will copy the table from A4 to J?what data do you mean, the title?, the entire column? or the activecell?

Please be more specific with your description!

Bob Phillips
05-08-2008, 09:56 AM
Sub test()
Dim Lastrow As Long

With Worksheets(1)

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:J1").Resize(Lastrow).Copy Sheets(2).Range("A1")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:AC1").Resize(Lastrow).Copy Sheets(3).Range("A1")
End With

With Worksheets(2)

For i = Lastrow To 5

If .Cells(i, "E").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

With Worksheets(3)

For i = Lastrow To 5

If .Cells(i, "E").Value <> "" Then

.Rows(i).Delete
End If
Next i
.Columns("D:J").Delete
End With

End Sub

david000
05-08-2008, 10:49 AM
Sub Macro1()
With Sheet1
.Range("e4").AutoFilter Field:=5, Criteria1:=""
.Range(Cells(5, "A"), Cells(.Rows.Count, "AC").End(xlUp)).SpecialCells(xlCellTypeVisible).Copy
Sheet3.Range("a2").PasteSpecial xlPasteValues
End With
With Sheet1
.Range("e4").AutoFilter Field:=5, Criteria1:="<>"
.Range(Cells(5, "A"), Cells(.Rows.Count, "J").End(xlUp)).Copy
Sheet2.Range("a2").PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Range("e4").AutoFilter
End With
End Sub

david000
05-08-2008, 11:29 AM
Sorry, slamet Harto
my example errors too easily xld's code is the way 2 go...

slamet Harto
05-12-2008, 02:36 AM
Hi Xld,
Apologize for the late replied.
Thanks you for your help. However, I think the code still need to be improved.
This because, if the data in column E5:E8, are complete (none of them are missing) then all data in the table will be copied to sheet 2 (Result expected 1).
In the contrary, if there are no data or some of the data inside range E5:E8 is missing, then the data will be copied to sheet 3 (Result expected 2) instead.
I'm realy newb in VBA, please help me again.
Also thanks to David and Simon.
Rgds, harto

Bob Phillips
05-12-2008, 02:42 AM
Oops, my error



Sub test()
Dim Lastrow As Long

With Worksheets(1)

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:J1").Resize(Lastrow).Copy Sheets(2).Range("A1")

Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1:AC1").Resize(Lastrow).Copy Sheets(3).Range("A1")
End With

With Worksheets(2)

For i = Lastrow To 5 Step -1

If .Cells(i, "E").Value = "" Then

.Rows(i).Delete
End If
Next i
End With

With Worksheets(3)

For i = Lastrow To 5 Step -1

If .Cells(i, "E").Value <> "" Then

.Rows(i).Delete
End If
Next i
.Columns("D:J").Delete
End With

End Sub

slamet Harto
05-13-2008, 02:04 AM
Dear Xld

Thank you so much and higly appreciate it.

Best, harto