0

I have three files. One excel file that is empty and contains my Macro. Second excel file that has about 20 tabs with updated data (Variable "UpdatedFiles" contains the file path) that needs to by copy/pasted into my third excel file (Variable "ProvisionFiles" contains the file path), that has extra tabs that link to the tabs that I am copy/pasting in.

My code works great right up to the point that I hit the Copy/Paste Section of my For Each Loop. Note that the tabs that I am copying over have the overlapping/same tab names in both workbooks.

I have tried to copy/paste data with all three methods described in this video. https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/

Still can't get it to work.

`Sub CopyPasteData()

Dim ProvisionFile As String 'String File Path of Provision File
Dim UpdatedFile As String 'String File Path of Updated OneSource Files
Dim ws As Worksheet 'Used to Loop Though WS Tabs in Updated OneSource Files
Dim wsName As String 'Name of Tab of OneSource File
Dim lastRow As Long
Dim lastColumn As Long


'Open Dialog Box that allows you to Select the Provision File
MsgBox "Select your provision file, which is the destination for the updated OneSource Reports. Please ensure this file is closed before opening."
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select file"
    .InitialFileName = "C:\"
    If .Show = -1 Then
        'ok clicked
        ProvisionFile = .SelectedItems(1)
        Workbooks.Open(ProvisionFile).Activate
        'Worksheets("Control").Activate
    Else
    'cancel clicked
    End If
End With

'Get updated Reports
MsgBox "Select the file that contains the updated OneSource Reports."
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select file"
    .InitialFileName = "C:\"
    If .Show = -1 Then
    'ok clicked
        UpdatedFile = .SelectedItems(1)
        Workbooks.Open(UpdatedFile).Activate
    Else
    'cancel clicked
    End If
End With

'Loop through Each tab in Updated File
For Each ws In Worksheets
    wsName = ws.Name
    lastRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row
    lastColumn = Sheets(wsName).Cells(7, Columns.Count).End(xlToLeft).Column
    'Debug.Print ("Yes")
    Workbooks(ProvisionFile).Worksheets(wsName).Range(Workbooks(ProvisionFile).Worksheets(wsName).Cells(1, 1), Workbooks(ProvisionFile).Worksheets(wsName).Cells(lastRow, lastColumn)) = Sheets(wsName).Range(Sheets(wsName).Cells(1, 1), Sheets(wsName).Cells(lastRow, lastColumn))

Next ws

End Sub`

Expected result is to finish the copy/paste loop.

Error I am recieving = Run-time error'9': Subscript out of range

Jeremy B.
  • 33
  • 4
  • That means that you either don't have a `Workbook` open with one of those names, or one of those workbooks doesn't have a `Worksheet` with that name. – BigBen Jul 18 '19 at 01:03
  • Also you're going to run into a problem with those `Cells` instances: see https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells. You need to specify the worksheet and workbook the cells are in/on. – BigBen Jul 18 '19 at 01:04
  • I have updated the code for your suggestion. It is still not working. I also editted my posted to show that the first bit of code is in fact opening the workbooks, so that is not the issue. – Jeremy B. Jul 18 '19 at 16:37
  • If it's opening the workbooks fine, then one of the workbooks doesn't have a `Worksheet` with that name. Check for typos, leading or trailing spaces, etc. – BigBen Jul 18 '19 at 16:38
  • Is that the only potential issue? Because I just verified that the tab names are the same. I dont think that is it. – Jeremy B. Jul 18 '19 at 16:45
  • Let's verify: `Dim ws1 as Worksheet`, then `Set ws1 = Workbooks(ProvisionFile).Worksheets(wsName)`. Does that throw an error? – BigBen Jul 18 '19 at 16:47
  • Yes it does throw an error. – Jeremy B. Jul 18 '19 at 16:52
  • Then the tab names are not the same... – BigBen Jul 18 '19 at 16:54
  • So you're right that the tab names being compared are not the same..... I think the issue is that for whatever reason, ProvisionFile is not being recognized as a true workbook (for whatever reason) and UpdatedFiles is being compared against my blank Macro Enabled Workbook which only contains "Sheet 1". Any Suggestions? – Jeremy B. Jul 18 '19 at 19:10
  • Use a `Workbook` variable instead of referring to the workbook by its name. – BigBen Jul 18 '19 at 21:11

0 Answers0