See comments in-line...

I appreciate you putting this all together in one post.

"Vacuum Sealed" wrote in message
ond.com...
Thx Clif

I appreciate your efforts, as with Bob's in helping me, I always ask
when something is not clear and always express my gratitude and
apprecitation when I am grinning from ear to ear when a project is up
and running.

That grinning from ear to ear feels pretty good, doesn't it?!


I use macro recording for many tasks, but I also keep a folder full of
handy bits of code I have read in the NG over the years and go to them
first to see if I can tweak them for my purpose.

Ron De'Brun website has been a tremendous source of help, as with all
other's who give of themselves freely.

There are lots of excellent websites mentioned from time to time in
these NGs. Ron's is only one of many. I keep thinking I should make a
list, but I've never gotten around to doing that.


As I stated earlier, I am no stranger to VBA as I use it in Access,
but Excel is not my strong-point (I liken it to the same differences
between Mexican, Portugese & Spanish, sound similar, but are
different)

Well put. VBA is the same, but the Application object models are quite
different.


Essentially everything I can explain as to what I needed is outlined
in original post, and then in last post, Bob has helped me through to
Step 4, and that's where I hit the wall so to speak, step 1 & 2 work
fine, step 3 although opens the History file, it does not set the
focus on the Sheet name required by matching it to the value in Master
E2. and step 4 does not copy the date into the first available blank
cell in Column B of the "Sheet-Value-Name" in History from Master D2.

Hmm. ws.Activate should set the focus properly. If it's not we need to
take another look at <something ... just not sure what right now. Have
you set a breakpoint at
Set ws = Worksheets(Worksheets("Master").Range("E2").Value) ? If the
Set is failing and getting trapped by the On Error Resume Next (ws Is
Nothing) that would cause what you just said.


To re-cap from the very last post:

.................................................. .................................................. ...........................

Essentially.

Master.xls

E2 has the month value to use as the value to set the sheet focus in
History.xls

D2 has the Date which needs to be copied

So, the range to be copied from Master will always be the same A5:Q300

then the focus is set on History, where the xlUp needs to find the
first
available black cell in B:B to do the following:

Paste the copied range

Go back to Master.xls D2 and copy the date, then back to History paste
it to
A:A and copy/loop it until the corresponding B cell next to it is
blank.
(Inserting the date beside each of the copied cells that were just
pasted)

.................................................. .................................................. .......................

This is what I have so far with help from Bob:

Sub BackupMaster()

Dim wb1 As Workbook
Dim wb2 As Workbook
-- Dim ws As Worksheets
--
Worksheets and Worksheet are two different objects and will provide
different properties and methods through Intellisense. You just defined
ws to be a worksheet *collection*. I'm not sure what happens when you
try to use it as a *worksheet*.


With Application
.ScreenUpdating = False
.EnableEvents = False
End With

On Error Resume Next
Set wb2 = Workbooks("History - 2011.xls")
On Error GoTo 0

If wb2 Is Nothing Then
Set wb2 = Workbooks.Open("E:\Wow Vic\Wow Scheduler\History -
2011.xls")
End If


On Error Resume Next
Set ws = Worksheets(Worksheets("Master").Range("E2").Value)
On Error GoTo 0

If Not ws Is Nothing Then
ws.Activate
** Set cell = ws.Range("A1").End(xlDown)

cell is now instantiated as the non-empty cell immediately above the
first blank cell below A1 (there could be more non-blank cells below
this one. If this is possible in your data, it complicates things and
needs to be taken into account.)

Set cell = ws.Range("A1").End(xlDown).Offset(1,0)

will instantiate cell as the first empty cell below A1. That
(confusing?) sentence brings up two points: I stringly recommend always
using Option Explicit and using declaring every variable you use -- that
makes it more difficult to inadvertantly use the same variable name in
different contexts and inadvertantly writing obscure bugs into your
code. Also, "cell" is a reserved word, and using it can be confusing to
both humans and the compiler - not a good idea.

Worksheets("Master").Range("D2").Copy cell

This statement will copy the value of D2 into cell ... not the range
that you are looking for.

End If

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

As I'm no Guru, I think this section ** should be different, something
like.

Set cell = ws.Range("A:A").End(xlUp) 'to find the first available
blank cell along Column A

the .End method in code does what happens when you use the END + arrow
key combination from the keyboard. How would you manually find the
first available blnak cell? You have to do the same thing in code.


Then again, this is why I fail most of the time as I don't understand
it strongly enough.

As I recall, Bob's suggestion was for you to record a macro for each
step of the process. (I have on occasion recorded one long macro of an
entire sequence. It works, but it can be more difficult to see what code
was generated by each step.) Have you doen that, and examined the
generated code? Knowing the answer to that question would help me know
how best to proceed.




I fully appreciate and understand if this is not achievable as time is
a premium for many today, I am no different, just let me know if it's
in the "Too-Hard-Basket" and I'll scrap the project.

This sounds pretty straight-forward to me. Work through what I've said.
If you havn't recorded a macro while doing this manually, I'd stongly
suggest that you do so, and examine the code carefully. You'll notice
that the macro recorder will use specific cell addresses, and then
operate on .ActiveCell, or .Selection. That code will have to be
revised (or re-written) to do what you are after, but it will give you
the bones to work with. I've set a watch on this thread ... come back
with your next set of questions!

You mention that you are pretty familiar with VBA in Access. Do you
consider yourself comfortable at the keyboard as an Excel user? If so,
then really all you need to do is learn the correlation between the
Excel UI and the object model Excel exposes to VBA. From there, coding
behind Excel will begin to feel as comfortable as coding behind Access.
I found that as I learned more about VBA in Excel it pushed me ahead in
Access; then the same thing happened again going back the other way.

Good luck!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)