Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
747 views
in Technique[技术] by (71.8m points)

vba - Copy cells between workbooks

Could someone please help me with some VBA code.

I am trying to copy 2 ranges of cells between workbooks (both workbooks should be created beforehand as i don't want the code to create a new workbook on the fly).

Firstly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell H5 to the last row in column H with data copy this to 'Sheet 1' of bookb.xls, starting in Cell B2 for as many cells down in the B column

Secondly I need to copy these ranges- From 'Sheet 3' of booka.xls, Range: Cell K5 to the last row in column K with data copy this to 'Sheet 1' of bookb.xls, starting in Cell D2 for as many cells down in the D column

Here is what I have so far:

 Sub CopyDataBetweenBooks()

Dim iRow        As Long
    Dim wksFr       As Worksheet
    Dim wksTo       As Worksheet

    wksFr = "C:ooka.xls"
    wksTo = "C:ookb.xls"

    Set wksFrom = Workbooks(wksFr).Worksheets("Sheet 3")
    Set wksTo = Workbooks(wksTo).Worksheets("Sheet 1")

    With wksFrom
        For iRow = 1 To 100
            .Range(.Cells(iRow, 8), .Cells(iRow, 9)).Copy wksTo.Cells(iRow, 8)
        Next iRow
    End With

End Sub
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Assuming you have the reference to wksFrom and wksTo, here is what the code should be

wksFrom.Range(wksFrom.Range("H5"), wksFrom.Range("H5").End(xlDown)).Copy wksTo.Range("B2")
wksFrom.Range(wksFrom.Range("K5"), wksFrom.Range("K5").End(xlDown)).Copy wksTo.Range("D2")

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...