将列从一个工作表复制到另一个工作簿

I have a workbook with multiple sheets in. I have a sheet called "MergedData", I need to copy columns B & L into a workbook called "Bookings.xls"

我有一本多页的工作簿。我有一个名为“MergedData”的表，我需要将B和L列复制到名为“Bookings.xls”的工作簿中

Column B is a Word Order number and L is a £value, I need it to copy over values that are over £0.01 and exclude everything with a nil value.

订单号和L列B是一个词一个£值,我需要复制/值/£0.01,排除一切一个零值。

The data will need to be placed into Rows of columns A & B in the Bookings workbook, starting at row 9.

从第9行开始，需要将数据放在booking工作簿中的A和B列中。

The data will need to erase every month when the code is run.

当代码运行时，每个月都需要删除数据。

I will be applying this code to multiple workbook for different Contractors. So the code cannot Hard-Lock to the original file. Needs to be the Active Workbook, the Bookings.xls file is a static workbook in all contractor folders.

我将把这个代码应用到不同承包商的多个工作簿上。因此，代码不能硬锁到原始文件。需要是活跃的工作簿，预订。xls文件是所有承包商文件夹中的静态工作簿。

Thanks for all the support.

谢谢大家的支持。

Private Sub CommandButton1_Click()
    Dim LastDRow As Integer, _
        InitWorkSheet As Worksheet, _
        DestWorkSheet As Worksheet, _
        myData As Workbook, _
        LastWRow As Integer

Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData") 'Or ActiveWorkbook.Sheets("Sheet1")

Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
DoEvents
Set DestWorkSheet = myData.Sheets("Sheet1") 'Or myData.Sheets("Sheet1")

With InitWorkSheet
    LastDRow = .Rows(.Rows.Count).End(xlUp).Row
    MsgBox LastDRow
    For i = LastDRow To 1 Step -1
        If .Cells(i, "L") < 0 Then
        Else
            LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row
            If LastWRow < 9 Then LastWRow = 9
            DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B")
            DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L")
        End If
    Next i
End With

myData.Save

End Sub`

1 个解决方案

#1

This this and let me know :

这让我知道:

Private Sub CommandButton1_Click()
    Dim LastDRow As Integer, _
        InitWorkSheet As Worksheet, _
        DestWorkSheet As Worksheet, _
        myData As Workbook, _
        LastWRow As Integer

Set InitWorkSheet = ActiveWorkbook.Sheets("MergedData")
Set myData = Workbooks.Open(ActiveWorkbook.Path & "\Bookings.xls")
Set DestWorkSheet = myData.Sheets("Sheet1")

DestWorkSheet.Cells.ClearContents

With InitWorkSheet
    LastDRow = .Rows(.Rows.Count).End(xlUp).Row
    For i = 1 To LastDRow 'To 1 Step -1
        If .Cells(i, "L") < 0.01 Then
        Else
            LastWRow = DestWorkSheet.Cells(DestWorkSheet.Rows.Count, "A").End(xlUp).Row + 1
            If LastWRow < 9 Then LastWRow = 9
            DestWorkSheet.Cells(LastWRow, 1) = .Cells(i, "B")
            DestWorkSheet.Cells(LastWRow, 2) = .Cells(i, "L")
        End If
    Next i
End With

myData.Save

End Sub