CODEWITHSUNDEEP
vbaexcel
Adamlh77
Adamlh77

Reputation: 19

Amending a VBA so that it works between two workbooks as opposed to two worksheets

Hi all and thanks in advance.

I currently have a VBA within my workbook to copy rows from "Demand Log" to "Change Log" when cells within column "O" have a specific value.

The VBA is working great, however I am now looking to split the two worksheets apart and have a separate workbook for each.

My question is - How can I change my VBA so that it copies and pastes between workbooks as opposed to between worksheets?

Please see my VBA code below:

Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Demand Log").UsedRange.Rows.Count
J = Worksheets("Change Log").Cells(Worksheets("Change Log").Rows.Count, "B").End(xlUp).Row
If J = 1 Then
   If Application.WorksheetFunction.CountA(Worksheets("Change Log").Range) = 0 Then J = 0
End If
Set xRg = Worksheets("Demand Log").Range("O5:O" & I)

Application.ScreenUpdating = False
For K = xRg.Count To 1 Step -1
    If CStr(xRg(K).Value) = "Change Team" Then
        J = J + 1
        With Worksheets("Demand Log")
            Intersect(.Rows(xRg(K).Row), .Range("A:Z")).Copy Destination:=Worksheets("Change Log").Range("A" & J)
            Intersect(.Rows(xRg(K).Row), .Range("A:Z")).Delete xlShiftUp
        End With
    End If
Next
Application.ScreenUpdating = True

Upvotes: 0

Views: 61

Answers (2)

JPR
JPR

Reputation: 642

If the workbook is open then you can refer to it like this: Workbooks("mybook.xls")[.method]

If the workbook is closed you need to open it: Workbooks.Open("C:\path\mybook.xls")[.method]

You can assign them to variables:

set wb = Workbooks("mybook.xls")
set wb = Workbooks.Open("C:\path\mybook.xls")

set ws = wb.Sheets("MySheet")

You can also get to the worksheet and assign it to a variable: (useful if you're working with a single sheet)

set ws = Workbooks("mybook.xls").Sheets("MySheet")
set ws = Workbooks.Open("C:\path\mybook.xls").Sheets("MySheet")

Untested, but give it a try:

Sub mysub()

    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long

    Dim wbDem As Workbook
    Dim wbChg As Workbook
    Dim wsDem As Worksheet
    Dim wsChg As Worksheet


    'Open/Get Workbook
    If Application.Workbooks("Demand.xls") Is Nothing Then
        Set wbDem = Application.Workbooks.Open("C:\path\Demand.xls")
    Else
        Set wbDem = Application.Workbooks("Demand.xls")
    End If

    'Open/Get Workbook
    If Application.Workbooks("Change") Is Nothing Then
        Set wbChg = Application.Workbooks.Open("C:\path\Change.xls")
    Else
        Set wbChg = Application.Workbooks("Change.xls")
    End If

    'Set Sheet Variables
    Set wsDem = wbDem.Worksheets("Demand Log")
    Set wsChg = wbChg.Worksheets("Change Log")


    I = wsDem.UsedRange.Rows.Count
    J = wsChg.Cells(wbChg.Rows.Count, "B").End(xlUp).Row
    If J = 1 Then
       If Application.WorksheetFunction.CountA(wbChg.Range) = 0 Then J = 0
    End If
    Set xRg = wsDem.Range("O5:O" & I)

    Application.ScreenUpdating = False
    For K = xRg.Count To 1 Step -1
        If CStr(xRg(K).value) = "Change Team" Then
            J = J + 1
            With wsDem
                Intersect(.Rows(xRg(K).Row), .Range("A:Z")).Copy Destination:=wsChg.Range("A" & J)
                Intersect(.Rows(xRg(K).Row), .Range("A:Z")).Delete xlShiftUp
            End With
        End If
    Next
    Application.ScreenUpdating = True

End Sub

Upvotes: 0

MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

You should refer to your worksheets and workbooks at the same time. So, instead of:

I = Worksheets("Demand Log").UsedRange.Rows.Count

You should type:

I = Workbooks("Book1").Worksheets("Demand Log").UsedRange.Rows.Count

anywhere in your code. For simplicity, you may set object variable, like:

Dim wb1 as Workbook
Set wb1 = Application.Workbooks("Book1")

or, better, set your worksheets as variables, for example:

Dim wsDemand as Worksheet
Set wsDemand = Workbooks("Book1").Worksheets("Demand Log")

and then you can use wsDemand instead of Worksheets("Demand Log") anywhere in your code. Book1 is of course default workbook's name, your file has probably other name.

Upvotes: 1

Related Questions