Copy Data From Closed Workbook in Open Workbooks Last Row

[Theken67]

I was able to use the Macro below to copy data from a closed workbook into my open workbook just fine. However, the final row in the open workbook will change as the Macro is run daily and the open workbook grows, so looking for help to change where the data pastes in the open workbook to the next row down from the last filled row. The data is pasting into "MasterData" in the active workbook. An added bonus would be if I can get the range where the data is coming from "UpdateData" to encompass columns A:I regardless of how many rows down it goes.

Sub Import_Template()
Dim wb As Workbook
Dim rng As Range
Dim lastRow As Long

Application.ScreenUpdating = False

Set wb = Workbooks.Open("L:\New Accounts\Andrew\Projects\Master File Template.xlsx", True, True)

Set rng = wb.Worksheets("UpdateData").Range("A2:I200")

With ThisWorkbook.Worksheets("MasterData")

rng.Copy
.Range("A8").PasteSpecial Paste:=xlValues
End With

wb.Close False

Set wb = Nothing

Application.ScreenUpdating = True

End Sub


Thanks!

 

[johnnyL]

How about:

Sub Import_Template()
'
    Dim lastRow         As Long
    Dim LastRowInRange  As Long
    Dim rng             As Range
    Dim wb              As Workbook
'
    Application.ScreenUpdating = False
'
    Set wb = Workbooks.Open("L:\New Accounts\Andrew\Projects\Master File Template.xlsx", True, True)
    LastRowInRange = wb.Worksheets("UpdateData").Range("A:I").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row   ' Get last Row Number of a range
'
    Set rng = wb.Worksheets("UpdateData").Range("A2:I" & LastRowInRange)                                ' Set rng = A2 thru I & Last row used in that range
'
    With ThisWorkbook.Worksheets("MasterData")
        rng.Copy
        lastRow = Range("A:I").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                      ' Get last Row Number of a range
        .Range("A" & lastRow + 1).PasteSpecial Paste:=xlValues
    End With
'
    Application.CutCopyMode = False                                                                     ' Clear Clipboard and 'marching ants'
'
    wb.Close False
    Set wb = Nothing
'
    Application.ScreenUpdating = True
End Sub

 

[Theken67]

How about:

Sub Import_Template()
'
    Dim lastRow         As Long
    Dim LastRowInRange  As Long
    Dim rng             As Range
    Dim wb              As Workbook
'
    Application.ScreenUpdating = False
'
    Set wb = Workbooks.Open("L:\New Accounts\Andrew\Projects\Master File Template.xlsx", True, True)
    LastRowInRange = wb.Worksheets("UpdateData").Range("A:I").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row   ' Get last Row Number of a range
'
    Set rng = wb.Worksheets("UpdateData").Range("A2:I" & LastRowInRange)                                ' Set rng = A2 thru I & Last row used in that range
'
    With ThisWorkbook.Worksheets("MasterData")
        rng.Copy
        lastRow = Range("A:I").Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                      ' Get last Row Number of a range
        .Range("A" & lastRow + 1).PasteSpecial Paste:=xlValues
    End With
'
    Application.CutCopyMode = False                                                                     ' Clear Clipboard and 'marching ants'
'
    wb.Close False
    Set wb = Nothing
'
    Application.ScreenUpdating = True
End Sub

This worked perfectly!! Thank you so much!

 

[johnnyL]

Glad to help @Theken67.

 

[Theken67]

Glad to help @Theken67.

Seeing if you can help with this...is there a way to add the code below (moves rows to the next sheet and deletes from the existing sheet in the same workbook) into the Macro fix you provided above? Basically what I am looking for it to do is:

The account number in the Master and Template files are both in Column A; if there is a "D" in Column I in the "Adjaccountimport" sheet (Master file) after the data is imported from the Template using the Macro you fixed above, I want it to move each instance of the same account number (Column A) to the "ClosedAccounts" sheet. I currently can move them using two separate macros where I run the import macro, and then run the move row macro, but just seeing if they can be combined into 1 macro or not.


Sub MoveRow()
Dim xRg As Range
Dim xCell As Range
Dim I As Long
Dim J As Long
Dim K As Long
I = Worksheets("Adjaccountimport").UsedRange.Rows.Count
J = Worksheets("ClosedAccounts").UsedRange.Rows.Count
If J = 1 Then
If Application.WorksheetFunction.CountA(Worksheets("ClosedAccounts").UsedRange) = 0 Then J = 0
End If
Set xRg = Worksheets("Adjaccountimport").Range("I1:I" & I)
On Error Resume Next
Application.ScreenUpdating = False
For K = 1 To xRg.Count
If CStr(xRg(K).Value) = "D" Then
xRg(K).EntireRow.Copy Destination:=Worksheets("ClosedAccounts").Range("A" & J + 1)
xRg(K).EntireRow.Delete
If CStr(xRg(K).Value) = "D" Then
K = K - 1
End If
J = J + 1
End If
Next
Application.ScreenUpdating = True
End Sub


Thanks!

 

[johnnyL]

Easiest way to do that would be to change the end of the Import subroutine from:

    Application.ScreenUpdating = True
End Sub

to:

    Call MoveRow
    Application.ScreenUpdating = True
End Sub

That will run the Import subroutine and then automagically run the MoveRow subroutine. That way you won't have to do it in two separate steps.

 

[Theken67]

Easiest way to do that would be to change the end of the Import subroutine from:

    Application.ScreenUpdating = True
End Sub

to:

    Call MoveRow
    Application.ScreenUpdating = True
End Sub

That will run the Import subroutine and then automagically run the MoveRow subroutine. That way you won't have to do it in two separate steps.

Once again coming through in the clutch...is there a way to work code into it though that will find the same account number that already exists as a Master entry in Column A and move that row as well?

So for example, the row being imported from the Template has account A4N000001 AND has a D in Column I (which results in it being moved to the next sheet with the subroutine Call MoveRow above), but that account number already exists in the Master file and needs to be moved as well...if that makes sense