When i set variables for sheet tabs, if i want to not use the typed name and use the vba name is it same as the others?
Dim ws1 As Worksheet: Set ws1 = wb.Sheets(1)
Dim ws2 As Worksheet: Set ws2 = wb.Sheets("Finaloutput")
or does it need to be As a object?
Dim ws1 As Object: Set ws1 = wb.Sheets(Sheet1)
or Set ws1 = wb.Sheets1
The first option should be fine. "Worksheet" is a an object itself, so that makes it more clear what you are defining it to be, instead of just a general "Object", which could include other objects.
**Important part**
Also i forgot in my real doc, i had to use merged cells on the output sheet (ws2). So in my test doc i know you cant copy paste data unless the source and location match the same merged cell count, in this case its 6 cells across merged. So i made a copy of the data on ws1 to the far right side in some hidden columns with this data all lined up so its easy to copy 1 big range of 9 columns for the data.
My issue is i need to paste it as values only. and for the life of me i cant figure out how to do it i keep getting errors.
Run-time error '1004':
Application-defined or object-defined error
ws2.Range("A18").PasteSpecial just doesnt seem to work anyway i use it..
I just want to copy rng.Copy into ws2.Cells(18, 1).Select on down so i guess from "A18:J" & LRow+7
I tried just selecting A18 and pasting normal and that works fine but I need to paste values only.
Get rid of your merged cells! Most serious programmers will NEVER use them! They are probably the single worst feature of Excel, and cause all sorts of issues for things like VBA, sorting, and other functionality. If you are simply merging cells across individual rows, it is much better to use the "Center Across Selection" formatting option instead, which gives you the exact same visual effect as merged cells, but without all the issues merged cells cause. See:
Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis
For the pasting values only, i came across this: ws2.Range("A18:J" & LRow + 7).Value = rng.Value
So it didnt use the copy paste function and worked good, unless theres a better way let me know.
If that does what you want, that is fine. That method is usually preferrable than using Copy/Paste, if it does what you want.
Also 1 last issue i came across on this.
On Col A i need to count down but only where there is a value > 0 in column H.
how can i do that? is there some function I can use to do this in that column A?
Place this formula in cell A10 and copy down for all rows:
Excel Formula:
=IF(H10>0,COUNT($H$10:$H10),"")
Note: Just a General "rule of thumb" to help you get the best help as fast as possible.
- If you are posting a follow-up question that is directly dependent upon the previous question, post it in the same thread.
- If your follow-up question is NOT directly dependent upon the previous question, post it in a new thread. That way it will appear as a new unanswered question, will get a lot more looks (because it will appear in the "Unanswered threads" listing that many use to look for new unanswered questions to answers, and stands a better chance of getting an answer (and faster!). Also, the initial replier to your original question may not be available at the time, or may not know how to answer your new question.
So your last question above (the one asking for the formula), is really a new question and would typically have been better to post in a new thread (it is simple question and probably would have gotten a reply hours ago, long before I logged on for the day).
