Write macro that will open a specific file path that is linked inside of an excel to then copy and paste back into the excel where the macro is stored

Question

So the goal of this macro is to open a specific file to then copy from that file and paste back into the original workbook where the macro is stored.

Items worth noting that makes this macro a little unique:

Workbook A (wbDestination) - A multi-worksheet excel, where the macro will be stored and where the information on Workbook B will be pasted in. The location of the file path for Workbook B will change monthly so I created a function within this Workbook A where the macro will be able to locate the file path successfully to meet the monthly changing of the file path location. I need the source data from Workbook B to be pasted into "Sheet2" on Workbook A. The file path location of Workbook B is in cell C2 on a worksheet called "Macros" within Workbook A.

Workbook B (wbSourceData) - A multi-worksheet excel, where the source data will be kept that I need to copy and paste into Workbook A. The source data I will be copying is on the second worksheet.

Sub CopyPaste()

    Dim wbSourceData As Workbook
    Dim wbDestination As Workbook
    Dim wsSourceData As Worksheet
    Dim wsDestination As Worksheet
    Dim strFName As String
    

    Set wbDestination = ThisWorkbook
    Set wsDestination = wbDestination.Sheets("Sheet2")
    
    strFName = wbDestination.Worksheets("Macros").Range("C2").Value
    
    Set wbSourceData = Workbooks.Open(strFName)
    Set wsSourceData = wbSourceData.Worksheets(2)
    
    
    wsSourceData.Range("A2").Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlToRight)).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
    wsDestination.Select
        Range("A4").Select
        Selection.PasteSpecial Paste:=xlPasteValues
    
    wbSourceData.Close SaveChanges:=False
    
End Sub

Some other things to note. I am successfully able to open Workbook B in this function but then the macro dies when I try to copy the data. The error I am getting in "Run-time error '1004': Select method of Range class failed". I also want to use the range selection functions xlToRight (twice) and xlDown on Workbook A because the number of rows of this source data will change monthly and this is the best way to grab the entire range. I will need the source data from Workbook B to be pasted back into Workbook A as Paste Values.

I am a beginner at VBA so whatever is needed to fix this macro can you please ELI5, thanks! I really appreciate it!

Variatus Variatus · Accepted Answer · 2020-09-16T01:24:18

Very nicely set up. I like the care you took with the naming. It's very helpful. But when it came to copy/paste you didn't read the chapter where they said "you need not select anything". Here is an improved version of that part of the code.

With wsSourceData
    ' last used cell in row 2 looking from right to left
    Cl = .Cells(2, .Columns.Count).End(xlToLeft).Column
    ' last used row in column A looking from bottom up
    Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
    Set Rng = .Range(.Cells(2, "A"), .Cells(Rl, Cl))
End With
Rng.Copy Destination:=wsDestination.Range("A4")

As you see, the job is to specify a Range. Of course, the Selection object is a range. Excel uses it to convey to its back office what the user has clicked on the screen. It isn't very well suited for anything else and therefore is used in place of a range whenever employed. Hence, go straight for the range if it's a range you need.

xlToRight looks for the next empty cell on the right. xlToLeft looks for first used cell from the right. Your code jumps over a blank cell to find the end. Same for looking from top down or from the end up. I hope there aren't any more filled cells to the right that you, in fact, want to exclude or from the top down. If so, the variables Rl and Cl must be found using the code you employed. Setting the range would be done with the same variables set differently.

Here are the declarations I added to the ones you already have.

Dim Rng             As Range
Dim Cl              As Long                 ' last used column
Dim Rl              As Long                 ' last used row

Write macro that will open a specific file path that is linked inside of an excel to then copy and paste back into the excel where the macro is stored

1 Answers