Anonymous
Anonymous asked in 電腦與網際網路軟體 · 8 years ago

如何使用VBA將不同Sheet的資料複製

如何使用VBA將不同Sheet的資料複製

將某一檔案裡有數個Sheet,

每個sheet儲存格裡自動找出--->2012這個字串或符號,

然後往右30個columns與往下40個Rows

複製到操作檔的Sheet1

檔案aa.xls裡

sheet1的內容裡

在a5有個符號(字串) (--->2012),

然後需要將

B5往右30個columns與往下40個Rows複製到操作檔的Sheet1

maybe在c22有個符號(字串)(--->2012),

然後需要將

B5往右30個columns與往下40個Rows複製到操作檔的Sheet1

...........

餘此類推

1 Answer

Rating
  • jan
    Lv 4
    8 years ago
    Favorite Answer

    http://www.FunP.Net/679840

    將壓縮檔案放在同一個資料夾,開啟 01132.xls 執行 RUN

    複製的範圍如果有誤解你的問題,請自行微調

    Sub test()

    Dim Asht As Worksheet, wbk As Workbook

    Dim c As Range, Frng As Range

    fn = ThisWorkbook.Path & "\aa.xls"

    Set Asht = Sheets("Sheet1")

    '以唯獨方式開啟aa.xls

    Set wbk = Workbooks.Open(fn, ReadOnly:=True)

    wbk.Activate

    '從第一列開始複製

    i = 1

    '被搜尋關鍵字

    txt = "2012"

    '歷遍 aa.xls 所有 Sheets

    For Each sht In wbk.Worksheets

    With sht.Cells

    '開始尋找

    Set c = .Find(txt, LookIn:=xlValues)

    If Not c Is Nothing Then

    firstAddress = c.Address

    Do

    '找到資料後開始複製

    Set Frng = c.Offset(0, 1).Resize(40, 30)

    Frng.Copy Asht.Cells(i, "A")

    i = i + 40

    Set c = .FindNext(c) '同一個Sheet 尋找下一筆

    Loop While Not c Is Nothing And c.Address <> firstAddress

    End If

    End With

    Next

    '關閉aa.xls,不存檔

    wbk.Close False

    End Sub

    • Login to reply the answers
Still have questions? Get your answers by asking now.