Need to push updates to closed workbook, having issues with source range

0
votes

As the title says, I'm trying to push contents of a range of cells from a source workbook to the same range in a target (closed) workbook. I'm using the following code:

Option Explicit

Sub UpdateAdminBook()


Dim MyPath          As String
Dim MyFile          As String
Dim Wkb             As Workbook
Dim Cnt             As Long

Application.ScreenUpdating = False

MyPath = "C:FILEPATH\" 'change the path accordingly

If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

MyFile = Dir(MyPath & "Administration.xlsx")

Cnt = 0
Do While Len(MyFile) > 0
    Cnt = Cnt + 1
    Set Wkb = Workbooks.Open(MyPath & MyFile)
    Wkb.Worksheets("Administration").Range("D18:D37").Value = ActiveWorkbook.Sheets("Administration").Range("D18:D37") 'change the new value accordingly
    Wkb.Close savechanges:=True
    MyFile = Dir
Loop

If Cnt > 0 Then
    MsgBox "Completed...", vbExclamation
Else
    MsgBox "No files were found!", vbExclamation
End If

Application.ScreenUpdating = True
End Sub

I'm having trouble starting at "ActiveWorkbook" and I keep getting "TRUE" or blanks. Any idea how I can fix this?

2
vbaexcel
Shouldn't MyPath = "C:FILEPATH\" be MyPath = "C:\FILEPATH\" (extra \ ) - chris neilsen
It's actually pushing to the correct range in the target workbook, but the content that's being pushed is my issue. "TRUE" or blanks appear in the correct range. I'm trying to get the contents from the same range in the source workbook - Juan Sebastian Medina
Once you open the workbook the active workbook changes to be the one you just opened. I think you need to assign active workbook to a variable at the start of the function and then use that. - Jerry Jeremiah

2 Answers

4
votes

When you open a workbook, it becomes the active workbook, rather than the original workbook. Change to this

Dim wbSrc As Workbook
Set wbSrc = ActiveWorkbook

'...

Do ...
    ' ...
    Set Wkb = Workbooks.Open(MyPath & MyFile)
    Wkb.Worksheets("Administration").Range("D18:D37").Value = wbSrc.Sheets("Administration").Range("D18:D37") 'change the new value accordingly
0
votes

you could assume your copying range as reference in a With - End With block

Sub UpdateAdminBook()
    Dim MyPath          As String
    Dim MyFile          As String
    Dim Cnt             As Long

    Application.ScreenUpdating = False

    MyPath = "C:FILEPATH\" 'change the path accordingly

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "Administration.xlsx")

    With ActiveWorkbook.Sheets("Administration").Range("D18:D37")
        Cnt = 0
        Do While Len(MyFile) > 0
            Cnt = Cnt + 1
            Workbooks.Open(MyPath & MyFile).Worksheets("Administration").Range("D18:D37").Value = .Value 'change the new value accordingly
            ActiveWorkbook.Close savechanges:=True
            MyFile = Dir
        Loop
    End With

    If Cnt > 0 Then
        MsgBox "Completed...", vbExclamation
    Else
        MsgBox "No files were found!", vbExclamation
    End If

    Application.ScreenUpdating = True
End Sub