• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy Paste to Workbook - Subscript out of range (Error 9) [SOLVED]

C

CalvinH

New Member
Please could someone help me with this error. I might not have my code 100%. I would like to copy the rows and paste it to the Worksheet specified.....

The error sits on .......Worksheets("Time_data").Select


Sub Copy_Cells()


Dim myData As Workbook

Dim x As Long


Worksheets("Time_data").Select


x = 18


Do While Cells(x, 75) <> ""


If Cells(x, 75) > "0" Then

Worksheets("Time_data").Rows(x).Copy

'Worksheets("Sheet1").Activate

Set myData = Workbooks.Open "C:UsershanniecDesktopDesktopSAPWasfieTimeSheetTableUpdate.xlsx")

eRow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Worksheets("Sheet1").Rows(eRow).PasteSpecial xlValues

Application.CutCopyMode = False


End If

x = x + 1


myData.Save


Worksheets("Time_data").Activate

Application.ScreenUpdating = True


Loop


End Sub
 
Hi, CalvinH!


First of all try this:

1) Add a 1st line on the module that holds the code to force you declare variables:

Option Explicit

2) Add this missing variable declaration:

Dim eRow as Long

3) Add a missing opening parenthesis at:

Set myData = Workbooks.Open("C:UsershanniecDesktopDesktopSAPWasfieTimeSheetTableUpdate.xlsx")

4) The code run fine thru your pointed line, so check if there's actually a worksheet called "Time_data" in that workbook.

Regards!

PS: 1) thru 2) are not really required but it's a good practice; if you want to omit them proceed from 3) in advance.
 
Calvin


When you are reviewing your code remember you don't need to select anything or activate anything when you are writing code. This is an unnecessary step 99.999% of the time. Just tell vb what you want to do and let vb do it.


Loops are slow and inefficient. Use filters or specialcells. You will be amazed at the speed differences to be gained.


I will finish this session by improving your code. You can pull all of your data from one book to the other in one foul swoop. This should run like lightning in comparison to what you have. It will get relatively faster the longer your data set.

[pre]
Code: 
Option Explicit
Sub OpenEx()
Dim owb As Workbook

Sheets("Time_data").Range("BW1", Sheets("Time_data").Range("Bw65536").End(xlUp)).AutoFilter 1, ">0"
Sheets("Time_data").Range("A2", Sheets("Time_data").Range("Bw65536").End(xlUp)).Copy 'Is the range appropriate
Set owb = Workbooks.Open("C:UsershanniecDesktopDesktopSAPWasfieTimeSheetTableUpdate.xlsx")
Sheets("Sheet1").Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues

owb.Close True
Sheets("Time_data").Range("BW18").AutoFilter
End Sub
[/pre]

I would prefer if the worksheet object number was used in the above. Like SirJB7 said make sure all the sheet names are identical in the code and in the workbook. This is where worksheet object numbers come into their own refering to sheet1 instead of Time_Data for example.


Take care


Smallman
 
Dear SirJB7 and Smallman, I don't know how to thank you.....you have save me loads of time...I am still learning VBA and this forum is really excellent. I appreciate you taking the time to help me.......you SOLVED my issue......MUCH MUCH APPRECIATED! God Bless Calvin
 
Hi Smallman,


Since we are dealing with xlsx [2007+] I'd use:

Code: 
Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues


than:

Sheets("Sheet1").Range("A65536").End(xlUp)(2).PasteSpecial xlPasteValues
 
Hi Shrivallabha


As Calvin's data set is 75 rows long, the solution I provided is entirely and probabably overly appropriate. You have to customise your solution for the problem.


I once used the method you suggesed. Then I realised the method I currently use is shorter and is fit for purpose in every file I ever use. I used to use your method till I realised that mine was shorter and appropriate 99.99% of the time.


Take care


Smallman
 
Hi, CalvinH!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
@shrivallabha

Hi!

Being purist none of the End(xlUp) codes will work if worksheet is fully filled in column A. Check this workaround:

http://chandoo.org/forums/topic/vba-colour-to-rows#post-35527

Regards!

PS: BTW, don't miss Luke M's comment, the 3rd below that one :)
 
Hi SirJB7


That link is an interesting thread. I would have enjoyed being apart of it. On a side note I could not open your file, XL found unreadable content. This may have been a problem at my end. It is a quiet day as I guess the 10 day no fly zone is starting to effect traffic on the site, so I am going to go into a bit of detail here (am sure Calvin wont mind).


In reference to the link above, when coding I almost always will not loop through a Range of cells. I will use SpecialCells and Filters to avoid this scenario, here is an example on this forum.


http://chandoo.org/forums/topic/vba-copy-values-sheet1-sheet2-a505a700-e2e197-but-stop-if-cell-goes-blank


The following is my Bible and I try to roll with these simple rules whenever entering the world of XL/VB. I think they should be in a sticky on top of all forums. They are appropriately titled The Golden Rules and were some of Dave Hawley’s best work.


http://www.ozgrid.com/forum/showthread.php?t=177019


I won’t disturb that older thread but if I were to go at that problem I would use the old chestnut Constants within the SpecialCells family.

[pre]
Code: 
Option Explicit
Sub ColorMeBad()
[A:N].SpecialCells(2, 23).FormatConditions.Add 2, , "=mod(row()+1,2)"
[A:N].FormatConditions(1).Interior.ColorIndex = 15 ‘Use Col N as eg.
End Sub
[/pre]

In my travels I have found the SpecialCells Method particularly useful when working with blanks, constants and visible cells. When used, it returns a Range Object that represents only the cells specified. For example, one can use the SpecialCells Method to return a Range Object that only contains formulae or in the example above, only constants. With this method you tend to be able to keep things as brief as possible.


Hopefully that adds to the natural order of things. It fills in a bit of time before I go to the movies to see Wolverine!!! Giddy up.


Take care


Smallman
 
@Smallman

Hi!


If you're referring to this link, it's still working fine for me without any unreadable file error, so if I were you I'd try from different computers; if you happen to fail let me know so as to upload it to a new location:

https://dl.dropbox.com/u/60558749/VBA%20Colour%20to%20rows%20%28for%20bobhc%20at%20chandoo.org%29.xlsm


Regarding the loop thru a range of cells I'd generally agree, but -as my friend b(ut)ob(ut)hc always says, there's always a but- in this case I stick to my loop: under any circumstance I'd set a conditional formatting condition for a million of cells (and neither for a lot less, no number pre-specified, only instinct also called common sense), just a matter of principles, mines of course... and I don't need to quote Groucho, do I? "Those are my principles, and if you don't like them... well, I have others.". But not in this case.


BTW very interesting links, I yet gave a look at them. But -again, yes- about the bible I should recognize that as I always say I'm a mix of x% atheist, y% heretic and z% agnostic, with x%+y%+z%>>100, of course. So I don't agree at all (in most cases, and a bit in just a few exceptions) with these -so called by the believers- commandments:


Excel "copper" (no way golden) rules:

1. Never Use Manual Calculation Mode, It's a False Reading Waiting to Happen! If You are Forced to use Manual Calculation, you HAVE a Bad Spreadsheet Design Which Should be Fixed, not Catered to.

4. Don't Mix Raw Data With Final Results, Reports or Data.

6. No Merged Cells (Use Center Across Selection Instead).

7. Avoid Empty Text ("") For Formula Results, Use Zero Instead. Tools>Options - View - Zero Values to Hide Globally. Or, Custom Format Like: 0;-0; To Hide Cell-By-Cell.

8. Avoid Array Formulae and Multiple Criteria SUMPRODUCT. Make Use Of PivotTables and/or Database Functions.

9. Don't Nest 2 Lookups To Avoid #N/A! Allow it to Occur and Reference Like: =IF(ISNA(A1),0,A1) and Hide the Lookup Column

10. Avoid Deleting Rows and Make Use of Auto Filter (AutoFilter),AdvancedFilter or Sort.

12. Avoid Formatting Cells as Text. Very Rarely is a Text Format Needed.

13. Never Store Numbers as Text.

14. Avoid Changing the Default Horizontal Alignment of Cells. Numbers, by Default are Right Aligned, While Text, by Default, is Left Aligned.


So I'm fully comfortable with 2, 3, 5, 11 & 15: 33%. I love sinning that 67%.


VBA "brass" rules (I'd love this part):

5. You Rarely Need to Select or Activate any Objects to Change Their Properties or Access Their Methods. If you MUST Select a Range Object, Use GoTo. <----- (almost never even under threat except for error handling or some type of data validation techniques, rare).

7. Avoid Loops. Make Good use of Much Faster Alternatives Like Find (Find Method), AutoFilter, AdvancedFilter, SpecialCells etc.

8. Loops Through Object Collections are the Fastest, Compared to Other Loop Types.

9. Don't Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.

10. Avoid Using Custom Functions When a Built-in Worksheet Function can be Used. Even Deeply Nested Worksheet Function are Often a Lot More Effiecient Than Custom Function Written in VBA.

11. Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort.


So, my sin rate passed the 70% threshold, I was yet beginning to worry. :p


I have my own Excel & programming (not just VBA) rules, which I think I've posted somewhere, let me see if I find them...

http://chandoo.org/forums/topic/user-form-via-qat#post-28628

http://chandoo.org/forums/topic/user-form-via-qat#post-28748

http://chandoo.org/forums/topic/data-validation-from-a-combined-list#post-99366

http://chandoo.org/forums/topic/automatic-date-update-in-a-cell-when-another-cells-value-changes-as-calculated#post-106300

... and I'm surely missing many of them.


Regards!
 
On MrExcel, you will find following in Aladin Akyurek's signature:

Assuming too much and qualifying too much are two faces of the same problem.


You have to judge what is needed and also personal preferences / inclinations kick in.
 
You must log in or register to reply here.
Back
Top