Trouble with copying a worksheet into a workbook that is not open

[Carty]

Hi. I am having trouble with some VBA code that asks for an order number and then uses the order number reply to file a copy of a worksheet into a workbook on a network (rather than my hard drive). The code I have currently is this:

Dim CurrentOrder As String
CurrentOrder = Inputbox("what is the current order number?", "Order Number")

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks("pathname & CurrentOrder").Sheets(8)

I would then like to rename the worksheet created in the network file with some text another variable something like this:

Sheets("Sheet1 (2)").Select
Sheets("Sheet1 (2)").Name = "ABC &Variable"

I get a run time error (9) subscript out of range on the underlined code which doesn't allow the rest of the code to operate so I don't know if the code to rename works either.

Can anyone point out where I have gone wrong?

Thanks

Paul

 

[Tim_Excel_]

Hi Paul,

First, I advise you not to use Select functions as they are unneccessary.
From what I am reading online, you will have to open up the workbook in the code in order to paste values to it.

Dim CurrentOrder As String
 CurrentOrder = Inputbox("what is the current order number?", "Order Number")

Workbooks.Open ("Filepath.Extension") '<- edit

Sheets("Sheet1").Copy After:=Workbooks(CurrentOrder).Sheets(8) 'variables do not need quotations, strings do

Workbooks("NAME").Sheets(9).Name = "ABC" & Variable

Then, you can close the Workbook with

Workbooks.Close("NAME") '<- edit

 

[Carty]

Hi Tim Thanks for the reply. I had a small issue when I edited your code. It is more likely to be my error than yours but here’s what I did so you can see where I went wrong:

Input box data entry = 12345 therefore CurrentOrder= 12345

Workbooks.Open ("Parent folder\folder\subfolder\ab12345cd.xlsx")
Sheets("Sheet1").CopyAfter:=Workbooks(CurrentOrder).Sheets(8)

My original coding may have thrown a spanner in the workshere. I had intended for the variable CurrentOrder to be used as part of the path name for the file. I have edited the code:

Sheets(“Sheet1”).copyafter:=Workbooks(“ab12345cd.xlsx”).Sheets(8)
Workbooks(ab12345cd.xlsx).sheets(8).name= “ABC” & Variable
Workbooks.Close("ab12345cd.xlsx")

The VBE didn’t like Workbooks.Close("ab12345cd.xlsx"). With a bit of research I got the compiler error to disappear by writing this

Workbooks(ab12345cd.xlsx).close SaveChanges:=True

Workbooks("NAME").Sheets(9).Name= "ABC" & Variable
I have to confess the NAME here threw me. So I’m not sure if the code above is right. Also could I have written this as
Workbooks(ab12345cd.xlsx).sheets(Sheet1).name= “ABC” & Variable

<light bulb=""> <light bulb> Given what you’ve said about quotes around strings not variables, I edited the open workbook command like this </light>
Workbooks.Open ("Parent folder\folder\subfolder\ab” &CurrentOrder & “cd.xlsx") and it opens the correct file
Again, using the example above,
Sheets("Sheet1").CopyAfter:=Workbooks(“ab” & CurrentOrder & “cd.xlsx”).Sheets(8)

The sub runs ok, asks me for the current order number, opens up the right file, asks to update the links and then falls over with a run time error 9 subscript out of range. When I press debug, the line
Sheets(“Sheet1”).copyafter:=Workbooks(“ab12345cd.xlsx”).Sheets(8) is highlighted
Workbooks(“ab12345cd.xlsx”).Sheets(8)is out of range

Sorry for the long winded reply but I am trying to understand what you have done rather than blindly pinching your code
Paul

 

[Tim_Excel_]

Workbooks("NAME").Sheets(9).Name= "ABC" & Variable

I have to confess the NAME here threw me.

I wrote 'name' in all caps, since that needs to be changed to your workbook's name.

Also could I have written this as

[FONT=Calibri][SIZE=3]Workbooks(ab12345cd.xlsx).sheets(Sheet1).name= “ABC” & Variable[/SIZE][/FONT]

The workbook name would need quotation marks. Like I mentioned before, strings need quotations marks. Right now, VBA treats the workbook name as a variable.



The Subscript is out of range error you are getting means VBA does not find a Workbook named "ab12345cd.xlsx", or sheet 8 is nonexistent in this workbook.

Do you want Sheet1 to be copied after the last worksheet in workbook "ab12345cd.xlsx"? Use

[FONT=Calibri][SIZE=3]Sheets(“Sheet1”).Copy After:=Workbooks(“ab12345cd.xlsx”).Sheets(Workbooks(“ab12345cd.xlsx”).Sheets.Count)[/SIZE][/FONT]

You can also drop the extension in the workbooks name (“ab12345cd.xlsx”->"ab12345cd"), although this should make little difference



In short, make sure your references are correct. A good way to check your variables when the code is running is to either put breaks in the code and then check the references by hovering your mouse over them, or to place

debug.print thevariablenameyouwanttocheck

lines in the code. The latter will print the variable's values to the Immediate screen.

 

[Carty]

Awesome! Thanks for the explanations, Tim. Seems I still need to work on the basics

 

[Tim_Excel_]

No problem Paul. Good luck with writing your code

 

[Carty]

Tim

The VBA still seems to have a problem finding the file it just opened!

I used this to open the file

 Workbooks.Open ("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”)

So now I have 2 files open: the master (containing “Sheet1”) and ab12345cd.xlsx where I want to copy “sheet1” to.



Because the ("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”) phrase worked to open the file, I copied and pasted it (to prevent typos) in later code

 Sheets("Sheet1").Copy After:=Workbooks("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”).Sheets(8)

This returns the same out of range error despite ab12345cd.xlsx having>10 sheets



I checked the CurrentOrder variable is correct by hovering over the variable in the last line of workable code (Workbooks.open ….).



I wondered if the VBA needed to be on the master sheet to transfer “Sheet1” to ab12345cd.xlsx so I entered

[CODE}Windows("master").Activate

Sheets("Sheet1").Copy Before:=Workbooks("ab" & CurrentOrder & "cd.xlsx").Sheets(8)

Sheets("Sheet1").Name = "abc" & Variable

ActiveWorkbook.Close ‘True [/CODE]



When everything is finalised I’ll un-comment the ‘true so that it saves the data



It worked!

Thanks again for your guidance and patience. The final code looks like this:

 Dim CurrentOrder As String

CurrentOrder = Inputbox("what is the current order number?", "Order Number")

Workbooks.Open ("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”)

Windows("master").Activate

Sheets("Sheet1").Copy Before:=Workbooks("ab" & CurrentOrder & "cd.xlsx").Sheets(8)

Sheets("Sheet1").Name = "abc" & Variable

ActiveWorkbook.Close 'true

 

[Tim_Excel_]

 Sheets("Sheet1").Copy After:=Workbooks("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”).Sheets(8)

The problem is that you're using the file's path instead of the file's name. You open the file with the file path, but you refer to open workbooks with their name or number.

 Sheets("Sheet1").Copy After:=Workbooks("ab” & CurrentOrder & “cd.xlxs”).Sheets(8)

As far as I am aware there is no need to activate the master window, as long as you use the correct references. Final code would look like:

CurrentOrder = Inputbox("what is the current order number?", "Order Number")

Workbooks.Open ("\Parent folder\folder\subfolder\ab” & CurrentOrder & “cd.xlxs”)

Sheets("Sheet1").Copy After:=Workbooks("ab" & CurrentOrder & "cd.xlsx").Sheets(8)

Sheets("Sheet1").Name = "abc" & Variable

Workbooks("ab" & CurrentOrder & "cd.xlsx").Close