Error while copying cells in another normal xls workbook when VBA Macro sheet is open

hiravibk

New Member
Joined
Nov 22, 2013
Messages
3
Hi,

I am facing a peculiar problem. I have built excel VBA macros for various functionalities like create dynamic excel workbooks based upon userform input values and some manipulations as required.

Now, when this VBA Macro excel sheet is open (not running). I am not able to copy any cells from another normal excel workbook(.xls). Getting "Subscript Out Of Range" with no error number. Attached for reference.

Could you please guide me here? Let me know if something is not clear.

Thanks In Advance,
Ravi

Other Info: Excel 2010, Win7
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Hi and Welcome to the Board,

I'm not clearly understanding what you mean by "this VBA Macro excel sheet is open". Do you mean a mean the Workbook with macros is open, or a Worksheet with event procedures is the Active Sheet?

If the error is directly from the Excel's debugger (not a MsgBox from the code's error handler), then a good place to start tracking down the problem is to click the "Debug" button. This will take you a highlighted line of code at which the execution has stopped. That line might refer to a worksheet that has been renamed or deleted which would generate a Subscript Out Of Range error.

If there's no Debug button, you could try Ctrl-Break while the error message is displayed to try to force a break to the debugger.
 

hiravibk

New Member
Joined
Nov 22, 2013
Messages
3
Thanks Jerry for your reply. Let me explain my situation again.

I have two excels objects opened 1) tool.xlsm, where in VBA macro code resides to do some manipulation. 2) Now, I open the existing normal xls file (somedata.xls). And while copying any cell data (say Cell A1, CTRL+C) from somedata.xls, boom! "Subscript Out Of Range" Error is thrown, caption of dialog box is Microsoft Visual Basic Applications. Yes, there is no Debug button in the message box, only OK and Help.

I tried to get into debug using CTRL+Break but it is not working, message box just disappears. and nothing else happens.

Then re reading your reply multiple times made me realize that ("refer to a worksheet that has been renamed or deleted") -

In my VBA code,
1) I am adding a new workbook (somedata1.xls)
2) Copying sheets from tool.xlsm(a,b)
3) deleting the default sheets (1,2,3).
4) do some manipulations and close the workbook.

So here the ones I was planning to copy content was from somedata1.xls (which is present in VBA project explorer) something just created by macro Or just opened a blank workbook and entered some details into Sheet1 and try to copy it. In both occassion, I hit Subscript out of Range Error.

So I created blank new workbook, renamed default sheets (say a, b, c), saved as new.xls and NOW, I am able to copy cell contents from it.

So here is my question to you Jerry,
1) If the above stated explaination is correct. How to get rid (clear) of newly created xls being clogged up in Project Explorer.? I do set the object = nothing once all the processing done in VBA code.
2) I will not able to copy the contents from default numbered sheets while the macro is open?

Thanks Again!
Ravi
 

hiravibk

New Member
Joined
Nov 22, 2013
Messages
3
Jerry,

I got the solution. It is a tricky miss from my side.

There are 3 macros in VBA code with userforms and one of them had the shortcut, CTRL+c to invoke it. So it was trying to invoke that UserForm and tried to load a values from a sheet(lookup) present in xlsm file. Now when I am trying to copy it from normal xls file with CTRL+c, its erroring which is not present in the file from which I am trying to copy contents using CTRL+c.

And I changed the settings Tools -> Options -> General -> Break On all Errors to capture this error.

Other thing I learnt is, not to give worksheets reference like set ws = worksheets("lookup") instead with workbook.worksheets("SheetName") as that is more specific.

Thanks all for help,
Ravi
 

Forum statistics

Threads
1,081,748
Messages
5,361,057
Members
400,611
Latest member
ThebigG

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top