当前位置: 动力学知识库 > 问答 > 编程问答 >

Copy and Paste data base of column data in VBA Macro excel

问题描述:

I am trying to copy and paste from one file to another file. I was able to copy data from the first file to the other file but is not quiet of what I need. What I need to to copy only specific data to the new file. . what I need from that file is to only copy and paste from row A TRNS and SPL with column E only the Triumph Foods LLC everything else I don't need to copy.

ENDTRNS

TRNS INVOICE 9/29/2016 Accounts Receivable BENEO Inc.

SPL INVOICE 9/29/2016 STORAGE INCOME:STORAGE:STORAGE A:A STORAGE

SPL INVOICE 9/29/2016 HANDLING INCOME:HANDLING A:A HANDLING

SPL INVOICE 9/29/2016 SALES TAX PAYABLE Treasurer of State

ENDTRNS

TRNS INVOICE 9/29/2016 Accounts Receivable Triumph Foods LLC

SPL INVOICE 9/29/2016 HANDLING INCOME:HANDLING C:C PALLET CHANGE OUT

SPL INVOICE 9/29/2016 HANDLING INCOME:HANDLING C:C WEIGHTS

SPL INVOICE 9/29/2016 FREEZE INCOME:FREEZE:FREEZE C:C FREEZE

SPL INVOICE 9/29/2016 HANDLING INCOME:HANDLING C:C SPACER REMOVAL

SPL INVOICE 9/29/2016 STORAGE INCOME:STORAGE:STORAGE C:C STORAGE

SPL INVOICE 9/29/2016 HANDLING INCOME:HANDLING C:C HANDLING

SPL INVOICE 9/29/2016 SALES TAX PAYABLE Treasurer of State

ENDTRNS

here is the part of the code I have to far it works but is not adding the rows for SPL.

Sub Distinct()

Const TRNS_START As String = "TRNS"

Const TRNS_END As String = "ENDTRNS"

Const COMPANY As String = "Triumph Foods LLC"

Dim searchRng As Range, copyRngStart As Range, copyRngEnd As Range

Set searchRng = Worksheets("Information").Range("A1")

' Enter/continue loop while A-column is non-empty

Do While searchRng.Value <> ""

' When we encounter the string TRNS in column A and Triumph Foods LLC in column E

If searchRng.Value = TRNS_START And _

searchRng.Offset(0, 4).Value = COMPANY Then

Set copyRngStart = searchRng ' Set the start of the copy area

End If

' When we encounter the string ENDTRNS

' (*and had a start cell already*)

If searchRng.Value = TRNS_END And Not copyRngStart Is Nothing Then

Set copyRngEnd = searchRng.Offset(-1, 5)

copyRngEnd.Worksheet.Range(copyRngStart, copyRngEnd).Copy _

Destination:=Sheets("Display").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Set copyRngStart = Nothing 'clear the "start" range

End If

Set searchRng = searchRng.Offset(1, 0)

Loop

End Su

b

Any help will be appreciated, thanks.

分享给朋友:
您可能感兴趣的文章:
随机阅读: