Issues with moving Sheets between Workbooks

Question

I'm working on a macro that will move a sheet from a selected Excel sheet into a document with a macro already loaded.

I'm having issues with actually getting the sheet to move over, I keep receiving a subscript out of range error and I'm unsure of why

I've perused stackoverflow and a few other resources so far. I've attempted using .sheets / workbook(workbookname).worksheets(1).copy ...so on and so forth.

Sub runEXCEL()

dim wb1 as workbook, wb2 as workbook
dim fd as filedialog
dim shtpath as string
dim ws as worksheet


Set fd = Application.FileDialog(msoFileDialogFilePicker)

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        shtpath = fd.SelectedItems(1)
    End If
Else
    End
End If

set wb1 = workbooks.open("c:\users\username\documents\yestbook.xlsm", true, false
set wb2 = workbooks.open(shtpath)
set ws = wb2.worksheets(1)
ws.name = "testname"
ws.worksheets(1).copy after:=wb1.sheets(1)
'xl.Application.Run "yestbook.xlsm!findCellAddress"

End Sub

Ideally I would like to copy a sheet from a selected workbook into my predefined workbook.

Why do you create 2 additional Excel processes (by `CreateObject`)? Can't you just run the code using the current Excel process? — Doc Brown, Jun 20 '19 at 20:30
This might be resolved if you avoid relying on [Activate/Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/), however, I think you cannot copy between `Application` instances like this. — David Zemens, Jun 20 '19 at 20:31
Also, `Workbooks("yestbook.xlsm")` is not qualified to an instance of the `Application`. So it's attempting to find `Workbooks("yestbook.xlsm")` in the *active* instance, which is `xl2`, which presumably does not contain that workbook you opened in `xl` instance. — David Zemens, Jun 20 '19 at 20:33
If `after:=xl.Workbooks("yestbook.xlsm").Sheets(1)` doesn't work, then your problem is that you can't copy between instances. If it does work, then problem is solved. But it will be easier to work with multiple isntances/workbooks if you scope variables to represent the several objects you're working with :) — David Zemens, Jun 20 '19 at 20:34
Tested & confirmed you will definitely get a 1004 error trying to copy between application instances. So you will need to refactor the code to work within a single instance of Excel in order to do this. — David Zemens, Jun 20 '19 at 20:36
@DavidZemens Do you have any suggestions on how I would be able to do that? I apologize, VBA isn't my primary language for coding, I've just recently started to learn it. — CryingOverVBA, Jun 20 '19 at 20:40
I had an error in my answer, should only be a minor revision to go back to `Dim ws As Worksheet`. BTW, compile error usually indicates that something is *wrong* with the code, not that you should use `Variant` (which basically swallows the compile error, and as you observed, leads to a runtime error instead). — David Zemens, Jun 20 '19 at 23:30
IOW, if something won't compile with strongly typed variable, it is not likely to work with `Variant` or generic `Object` types, either :) — David Zemens, Jun 20 '19 at 23:31

score 0 · Answer 1 · answered Jun 20 '19 at 20:41

0

It is not necessary to create additional Excel processes (actually, this might be cause of the problem). You should also use workbook and worksheets variables for all sheet access, and avoid unqualified access like Sheets("testname").

Try something along the lines of:

Dim wb as workbook, ws as worksheet, wb2 as workbook, ws2 as worksheet

Set wb = Workbooks.Open(mysheetpath1)
Set ws=wb.Worksheets(1)

set wb2=Workbooks.Open(mysheetpath2)
set ws2=wb2.Worksheets(1)
ws2.Copy after:=ws

answered Jun 20 '19 at 20:41

Doc Brown

19,739
7
52
88

Using a solution like this, is there a way that I'm able to make the workbook visible? I did try doing ws.visible = true but to no avail. – CryingOverVBA Jun 20 '19 at 21:15
@CryingOverVBA: use `ws.Activate` and `wb.Activate`, depending on what you are trying to accomplish. ` – Doc Brown Jun 20 '19 at 21:59
I thank you for your input, although I don't think this does what I'm attempting to accomplish. I think that the ws.activate and wb.activate will only keep the program active in the background, although I'm trying to view the workbook once yestbook is open. I tried using wb1.visible = true but that doesn't seem to work. Is there a different way that I might be able to accomplish this? – CryingOverVBA Jun 21 '19 at 15:49
@CryingOverVBA: it seems you are mixing up the application object, workbook objects and worksheet objects. Since you removed the unnecessary additional Excel application objects, there is only one running Excel process (which is the same one which executes the VBA), and it should be already visible, if you did not mess around with the "Visible" attribute. If you did, add a line "Application.Visible=True" to fix this. That, however, does not control which of the open documents (workbooks) is active/in foreground, that is what `wb.Activate` is for. ... – Doc Brown Jun 21 '19 at 16:32
... Finally, `ws.Activate` will bring the related sheet (tab page) inside its workbook into foreground. I hope this helps. – Doc Brown Jun 21 '19 at 16:33

David Zemens · Accepted Answer · 2019-06-20T21:52:19.407

0

Subscript out of range occurs because "yestbook.xlsm" does not exist in the global Workbooks collection, which is in this case limited to the active instance of Excel (i.e., the instance from which this code is executed). You've opened two workbooks, each in a new and separate instance of Excel, which presents further problems, because you can't actually Copy worksheets between instances like this.

This should work, unless there are some extraordinary reasons why each file must open in its own instance:

Dim wb1 As Workbook, wb2 as Workbook
Dim fd As FileDialog
Dim shtpath As String
Dim ws As Worksheet

Set fd = Application.FileDialog(msoFileDialogFilePicker)

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        shtpath = fd.SelectedItems(1)
    End If
Else
    End
End If

Set wb1 = Workbooks.Open("C:\Users\username\Desktop\yestbook.xlsm", True, False)
Set wb2 = Workbooks.Open(shtpath)
Set ws = wb2.Worksheets(1)
ws.Name = "testname"
ws.Copy after:=wb1.Sheets(1)

edited Jun 20 '19 at 21:52

answered Jun 20 '19 at 20:42

David Zemens

53,033
11
81
130

I went ahead and tested this code and I had to make a tweak to it by changing worksheet to variant for ws. Although, when I do this, I start getting a run-time error 438 "object doesn't support this property or method". Do you happen to know if this is caused because I changed worksheet to variant? – CryingOverVBA Jun 20 '19 at 21:36
@CryingOverVBA I can't imagine why you'd need to use a `Variant` rather than `Worksheet`. Can you edit your question with the code you tried using `Worksheet`? I'd like to see what/where error occurs there. – David Zemens Jun 20 '19 at 21:42
I changed over to variant because with declaring the variable as worksheet it gives me a compile error specifically on ws.worksheets(1).copy after:=wb1.sheets(1), more specifically the ".worksheets". I'll go ahead and edit my code in my question though. – CryingOverVBA Jun 20 '19 at 21:48
There's an error in my answer :) Do `As Worksheet` and then `ws.Copy After:=wb.Sheets(1)`. – David Zemens Jun 20 '19 at 21:51
You've been extremely helpful. I was curious if you knew how I could go about making the worksheet visible? I tried using wb1.visible = true and other variations of the sort but I wasn't able to get the sheet to become visible. – CryingOverVBA Jun 21 '19 at 15:23
`wb1.Visible` would refer to the *workbook*, not the *worksheet*, and there isn't a `Visible` property on a workbook, so that would error. Which sheet is currently hidden and needs to be made visible? You would just do `.Visible = True`, I think. But you need to reference the sheet of course. – David Zemens Jun 21 '19 at 15:26
I'm looking at making the entire yestbook.xlsm unhidden, as the function that is called later turns something into a report(of sorts). – CryingOverVBA Jun 21 '19 at 15:51
not sure what you mean by "making the entire yestbook unhidden". Do you mean "all sheets should be unhidden"? Or are you implying that the workbook itself is not visible? Because it should be... – David Zemens Jun 21 '19 at 15:53
My entire workbook itself is not visible. I can't seem to get this resolved despite checking multiple different sources. I don't want to seem like I'm unappreciative, you've been extremely helpful to me! Thank you so much. – CryingOverVBA Jun 21 '19 at 18:45
Close ALL instances of Excel. Kill any running instances from Excel via Task Manager (there may be some hidden instances still running). If you open the file manually (via double-click, for instance) can you see it? – David Zemens Jun 21 '19 at 18:55

score 0 · Answer 3 · answered Jun 21 '19 at 11:03

0

Second to last line - you have

ws.worksheets(1).copy after:=wb1.sheets(1)

This should be

wb.worksheets(1).copy after:=wb1.sheets(1)

it's a typo on the second char

answered Jun 21 '19 at 11:03

Harassed Dad

4,669
1
10
12

Issues with moving Sheets between Workbooks

3 Answers3