Hi Everyone

Need some help please:

Although I have a reasonably good understanding of VBA, I fail miersably
with Excel VB, and as such, I only have a small amount of VB knowledge, that
said, I need to create a VB Code to execute the follwing scenario via
CmdBtn.

Firstly:

Master & MastHist are located in the same folder, MastHist is purely an
Archival File for the year.

1. check if "MastHist - 2011.xls" is open, If not, then open it.

2. Remove color formatting from "Master"

3. Check Cell from "Master", "E2" for applicable Sheet to set focus to, eg
if "E2" = "Jan" then "MastHist - 2011" sheet focus will be "Jan"

4. Copy Cell from "Master", "D2" then set focus on "MastHist - 2011", "Jan"
or which-ever (Sheet/Month) has the focus and find the first available Cell
in Column A:A, Paste the Data.(Date)

5. Copy first Row available (from bottom Up) from "Master", between Column
A:Q, then paste it to "MastHist - 2011", "Jan" or which-ever (Sheet/Month)
has the focus and find the first available Cell in Column B:B, Paste the
Data. Loop this until all cells up to and including Row 5 (Excluding rows 1
to 4) of Master have been copied across to MastHist.

6. Set focus on "MastHist - 2011", "Jan" or which-ever (Sheet/Month) has the
focus, select range A1:Q4000 (Each sheet will not exceed 3000 rows but have
added extra 1000 just to be safe), then sort by "A" - Ascending.

7.Whilst focused on MastHist, Save.

Secondly:

In a seperate Sub()

8. Set focus on "Master" Check for underlying VB Code attached to Workbook
and Modules and delete all Code without the need to save Modules to another
location.

9. Delete Rows 1, 2 & 3.

10. SaveAs T:\MyFolder\Filename - "dd-mmm-yy".xls

I really appreciate any assistance you can afford me.

TIA
Clueless

Take it one step at a time

Step 1

On Error Resume Next
Set wb = Workbooks("MastHist - 2011.xls")
On Error Goto 0

If wb Is Nothing Then

Set wb = Workbooks.Open("C:\some folder\MastHist - 2011.xls")
End If

You can use the macro recorder to do step 2.



HTH

Bob

"Vacuum Sealed" wrote in message
nd.com...

Hi Everyone

Need some help please:

Although I have a reasonably good understanding of VBA, I fail miersably
with Excel VB, and as such, I only have a small amount of VB knowledge, that
said, I need to create a VB Code to execute the follwing scenario via
CmdBtn.

Firstly:

Master & MastHist are located in the same folder, MastHist is purely an
Archival File for the year.

1. check if "MastHist - 2011.xls" is open, If not, then open it.

2. Remove color formatting from "Master"

3. Check Cell from "Master", "E2" for applicable Sheet to set focus to, eg
if "E2" = "Jan" then "MastHist - 2011" sheet focus will be "Jan"

4. Copy Cell from "Master", "D2" then set focus on "MastHist - 2011", "Jan"
or which-ever (Sheet/Month) has the focus and find the first available Cell
in Column A:A, Paste the Data.(Date)

5. Copy first Row available (from bottom Up) from "Master", between Column
A:Q, then paste it to "MastHist - 2011", "Jan" or which-ever (Sheet/Month)
has the focus and find the first available Cell in Column B:B, Paste the
Data. Loop this until all cells up to and including Row 5 (Excluding rows 1
to 4) of Master have been copied across to MastHist.

6. Set focus on "MastHist - 2011", "Jan" or which-ever (Sheet/Month) has the
focus, select range A1:Q4000 (Each sheet will not exceed 3000 rows but have
added extra 1000 just to be safe), then sort by "A" - Ascending.

7.Whilst focused on MastHist, Save.

Secondly:

In a seperate Sub()

8. Set focus on "Master" Check for underlying VB Code attached to Workbook
and Modules and delete all Code without the need to save Modules to another
location.

9. Delete Rows 1, 2 & 3.

10. SaveAs T:\MyFolder\Filename - "dd-mmm-yy".xls

I really appreciate any assistance you can afford me.

TIA
Clueless

Thank you so much Bob.

Steps 1 & 2. Done.

Sorry Bob

Forgot to mention that I'm using 2003.

Cheers

Okay, a couple more

Steps 3 & 4

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

If Not ws Is Nothing Then

ws.Activate
Set cell = ws.Range("A1").End(xlDown)
Worksheets("Master").Range("D2").Copy cell
End If

I am not really sure I understand Step 5.


HTH

Bob

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

Thank you so much Bob.

Steps 1 & 2. Done.

Bob

Thx again

With Step 5, I wasn't sure about it as I have read some posts that mentioned
that when copying a range of cells or individual rows you count from the
bottom and loop until you find the end row which you specify to stop at.

Either that or just make a range.selection and then got the target sheet,
then count from the bottom to find where to paste the range.

I wasn't sure...

Regards
Mick

Hi Bob

So far it's going well, with two exceptions.

1.
Set ws =Worksheets(Worksheets("Master").Range("E2").Value 2)

although it opens the workbook, it does not set the sheet focus to the value
in E2, it remains focused on whatever sheet it was last used before
saving/closing.


2.
Set cell = ws.Range("A1").End(xlDown)
Worksheets("Master").Range("D2").Copy cell

It is not copying the value from D2 to A1.

Thx again
Mick

Newcomer to this thread.... comments inline.

"Vacuum Sealed" wrote in message
ond.com...
Hi Bob

So far it's going well, with two exceptions.

1.
Set ws =Worksheets(Worksheets("Master").Range("E2").Value 2)

although it opens the workbook, it does not set the sheet focus to the
value in E2, it remains focused on whatever sheet it was last used
before saving/closing.

try changing that 1 line to two lines:

Set ws =Worksheets(Worksheets("Master").Range("E2").Value 2)
ws.activate



2.
Set cell = ws.Range("A1").End(xlDown)
Worksheets("Master").Range("D2").Copy cell

It is not copying the value from D2 to A1.

No .. that code will copy the value of D2 into the last filled cell
below A1. Is that what you want?

To copy D2 into A1, try:

ws.Range("A1") = Worksheets("Master").Range("D2")

Is there another reason you need the "Set cell = ..." ?


--
Clif McIrvin

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

Hi Clif

In all likelihood I wasn't very clear when I put my original thread
together, and Bob has been helping in stages with only sketchy explaination.

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)

Thats it

TIA
Mick

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

In all likelihood I wasn't very clear when I put my original thread
together, and Bob has been helping in stages with only sketchy
explaination.

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)

Thats it

TIA
Mick




I just took the time to pull up the entire thread ... and see that I had
read 2 or 3 of the early posts but had forgotten. I'm with Bob in
taking things one step at a time. When I started beating my head against
Excel VBA about 3 years ago the frustration level was pretty high. The
more I fought with it, the more familiar I became with the tools
available - I found that learning how to actually use the built-in help
files was not particularly easy, but proved well worth the effort; as
did becoming familiar with using these newsgroups. Now, although there's
a lot about the capabilities of Excel and VBA behind Excel that I
haven't even touched yet I am able to get around fairly easily.

One of the learning tools is to use the macro recorder to record a
sequence of manual commands, then take the time to master what the
generated code is doing (and *how* it is doing it.) I found that
clicking on a statement that I wasn't sure about and pressing F1 to
bring up the help topic for that method or property taught me a lot
about how to use (and understand!) the available help.

Some while back I undertook a project a bit similar to yours- every
month I gather a range of cells (by category) from a master detail
workbook and copy them into various detail summary workbooks. The
process also involves copying down a series of formulae, moving a chart
and resizing the print area. I used the macro recorder to record the
entire process (for one category), then worked with it until I
understood the objects, properties and methods in the generated code and
was able to generalize it to become a useful macro (in this case, a
series of macros.)

Now -- with all that said: I'm willing to help with your project, but I
really don't have the time to gather all the bits and pieces of the
thread and put them all together so I have "the big picture." It would
be really helpful for me if you would include the relevant context in
your replies - because the thread is not in front of me. All I have
(without taking the time to go back and get previous posts) is the post
that I am reading.

If you combine your description of what you need, Bob's steps, and what
you have so far into one posting it'll be much easier to review, and
suggest your next step.

(BTW, I did notice that your latest questions regarding Bob's steps 1
and 2 suggest that you missed part of Bob's provided code.)

Also, if something one of us volunteers suggests leaves you confused
don't be afraid to reply, quoting the code or instruction that's giving
you trouble, and ask for an explanation. Many of us prefer to assume
that 'you' already know 'more', not 'less' so we often don't explain our
suggestion.

--
Clif McIrvin

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

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.

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.

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)

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.

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

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)
Worksheets("Master").Range("D2").Copy cell
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

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


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.

Cheers
Mick

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 :-)

Clif

I appreciate the steps.

I decided to go and look over some older projects I had done over the years
and found a handy work-a-round for the Set Focus issue not working.

I employed the Case Select argument with great results.

On Error Resume Next
Windows("WowSchedMaster.xls").Activate

If Not Range("E2").Value Is Nothing Then

Select Case True

Case Target.Value = "Jan"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Jan").Select

Case Target.Value = "Feb"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Feb").Select

Etc.........

Thx again

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

I appreciate the steps.

I decided to go and look over some older projects I had done over the
years and found a handy work-a-round for the Set Focus issue not
working.

I employed the Case Select argument with great results.

On Error Resume Next
Windows("WowSchedMaster.xls").Activate

If Not Range("E2").Value Is Nothing Then

Select Case True

Case Target.Value = "Jan"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Jan").Select

Case Target.Value = "Feb"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Feb").Select

Etc.........

Thx again


I believe that the only practical difference between what you have here
and Bob's code is that you are using .Select on the sheet, where Bob
used .Activate in his code. Did you try changing the "Dim ws as
Worksheets" to "Dim ws as Worksheet" ??

Do you need to see what the macro is doing? It's really not necessary to
use .Activate or .Select at all in code, because VBA can operate
directly on ranges. Unless you *need* to see what is happening, it
seems to me that the biggest thing Select and Activate do is slow down
your macro.

To say that another way, it seems to me that the purpose of .Select and
..Activate is for setting up the UI, not for operating on the
workbook/worksheet.

I've found that there are usually multiple different solutions to any
given task. <g

--
Clif McIrvin

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

Thx again Clif

I will explore the Dim issue.

I'm reasonably confident I can get most of the balance done on my own, with
one exception.

I have decided to do all the changes within the Master and paste the entire
range into History, so once I have the focus set back on History with the
copied range what is the best solution to find the first blank cell in
Column A:A, xlUp until value = Not "" or xlDown until Value = "".

Once this has been formulated and overcome then the rest is fairly
straightforward.

TIA
Mick

As a follow up, changing the Dim statement did not fix the issue.

In as much as the snippet of code I'm using is probably long-winded, it's
practical as it serves it's purpose and frees me up to move forward.

I will look at it later after the project is up and running as the window of
oportunity is closing on this project for me.

Thx again

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

I will explore the Dim issue.

Since that didn't resolve it, I'm thinking walking through what is
happening there by using breakpoints will be the fastest way to proceed
when you do choose to track down why it's not working.


I'm reasonably confident I can get most of the balance done on my own,
with one exception.

I have decided to do all the changes within the Master and paste the
entire range into History, so once I have the focus set back on
History with the copied range what is the best solution to find the
first blank cell in Column A:A, xlUp until value = Not "" or xlDown
until Value = "".

I'm thinking that I showed you .Offset(1,0) to drop down to the cell
below (with no quoted context in your post I can't confirm that). If
there will never be any empty cells, then xlDown will work just fine.

I've never needed to do what you need - someone else may wish to weigh
in with a suggestion. Instead of using xlDown, a variation of

Selection.SpecialCells(xlCellTypeLastCell).EntireR ow.Cells(2,
1).Select

might do what you are looking for.

Here's a short macro to illustrate using [ END + down ] and [GoTo
Special | Last Cell ]
To use this code, run it from a blank worksheet.

Option Explicit

Sub Example()
'
' Example Macro
' Find first empty row - example beginning with a new sheet
'

'
' create an 'outer boundary'
Range("H15").Select
ActiveCell.FormulaR1C1 = "x"

' populate some cells
Range("A1").Select
ActiveCell.FormulaR1C1 = "a"
Selection.AutoFill Destination:=Range("A1:A7"), _
Type:=xlFillDefault
Range("A1:A7").Select

' arbitrarily choose one of the populated cells
Range("A4").Select
Selection.End(xlDown).Select
'"A7" is now the active cell; "A8" would be the first
'empty cell in this column - but:

Selection.SpecialCells(xlCellTypeLastCell).Select
' "H15" defines the last populated cell on the worksheet.

' and "A16" would be the first cell below all data
Range("A16").Select
End Sub


--
Clif McIrvin

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

Hi Clif

Though it would be better to star another line on this thread as the other
was getting long.....LOL.....

Anyway, I have got to a point where I am happy, "to-a-point"

I am up to the point where I have the range I need to copy to the History
sheet exactly the way I need it. Now..!! all I need to do once the focus is
back on History, is to find a nice piece of code that will find the first
available cell in A:A so that I can paste the range in.

If you or anyone else can help, that would pretty much put me on the home
stretch and put an end to this leg of the project.

Sincerely Clif, you have been great with your mentoring from where Bob had
left off (All good Bob)....

That said, I still have no clue as to the offset xlDown or up for that
matter, although I have no probs with horizontal offset and value inserts
(Go figure).

I have literally been bashing my head on the keyboard trying to get my head
around the OffSet problem, which is why I kinda went back a few steps and
looked at older projects that looked kinda like what I wanted and pieced it
all together.

It's not a pretty code, but it's functional to the point where I don't feel
the need to pick my monitor up and throw it out the window....

Thx again.

Cheers
Mick

Code below:


Sub BackupWowSchedule()

Dim Swb As Workbook
Dim Twb As Workbook
Dim ws As Worksheet
Dim BackupFilePath As String
Dim BackupFileExtStr As String
Dim BackupFileName As String
Dim FileExtStr As String

' Turns Screen Updating / Blinking off

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

Set Swb = ActiveWorkbook

'Inserts a Column so the Date can be copied into "A5"

Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

'Changes the Font to "Verdana - Size 8"

With Selection.Font
.Name = "Verdana"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With

'Changes the Date format to "dd-mmm-yy" which gives you "01-Jan-11"

Range("A5:A200").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"

'Copies the Date from "E2" and Pastes it into "A5"

Range("E2").Select
Selection.Copy
Range("A5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

'Creates the formula that will determine how many cells will display the
Date

Range("A6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=0,"""",R[-1]C)"

'Copies new date formula to respective cells below, upto Row 200

Range("A6").Select
Selection.Copy
Range("A7:A200").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

'Sorts the sheet by Vendors in Ascending Order

Range("A4:R200").Select
Selection.Sort Key1:=Range("E5"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

'Inserts the Count value for each day which is used to sum the total
number of days worked in the month

Range("R5").Select
ActiveCell.FormulaR1C1 = "1"
Range("A5").Select

'Checks if WowScheduleHistory is "Open or "Closed", if closed it is then
opened

On Error Resume Next
Set Twb = Workbooks("WowSchedHistory - 2011.xls")
On Error GoTo 0
If Twb Is Nothing Then
Set Twb = Workbooks.Open("E:\Wow Vic\Wow Scheduler\WowSchedHistory -
2011.xls")
End If

'Sets the focus on the Month-Sheet of WowSchedHistory using value in
"E2" of WowSchedMaster

On Error Resume Next

Windows("WowSchedMaster.xls").Activate

If Not Range("E2").Value Is Nothing Then

Select Case True

Case Target.Value = "Jan"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Jan").Select

Case Target.Value = "Feb"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Feb").Select

Case Target.Value = "Mar"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Mar").Select

Case Target.Value = "Apr"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Apr").Select

Case Target.Value = "May"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("May").Select

Case Target.Value = "Jun"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Jun").Select

Case Target.Value = "Jul"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Jul").Select

Case Target.Value = "Aug"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Aug").Select

Case Target.Value = "Sep"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Sep").Select

Case Target.Value = "Oct"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Oct").Select

Case Target.Value = "Nov"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Nov").Select

Case Target.Value = "Dec"
Windows("WowSchedHistory - 2011.xls").Activate
Sheets("Dec").Select

End Select

On Error GoTo 0

End If

'Sets the focus back onto WowSchedMaster

On Error Resume Next
Windows("WowSchedMaster.xls").Activate
On Error GoTo 0

Range("A5:R200").Select

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

Though it would be better to star another line on this thread as the
other was getting long.....LOL.....

yup - that branch was getting long <g


Anyway, I have got to a point where I am happy, "to-a-point"

Great!


I am up to the point where I have the range I need to copy to the
History sheet exactly the way I need it. Now..!! all I need to do
once the focus is back on History, is to find a nice piece of code
that will find the first available cell in A:A so that I can paste the
range in.

If you or anyone else can help, that would pretty much put me on the
home stretch and put an end to this leg of the project.

Did you catch my post in the other branch last evening (4:28, I think)?
From that post, try this:

Range("A5:R200").Select

Range("A5").Select
Selection.SpecialCells(xlCellTypeLastCell).EntireR ow. _
Cells(2, 1).Select


Thanks for the kind words. It's good to have to opportunity to repay a
bit of what I've learned from the volunteers in these ng's. Glad to hear
you're making progress!

I didn't more than glance at your code ... it made sense! Quite likely
if you keep doing this sort of thing, in a couple years you'd do it
differently, and that's just fine. What you have will get the job done.
Maybe it can be speeded up, but fine tuning comes after getting it
going! <g

--
Clif McIrvin

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

What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window. I've
spent last night and most of this afternoon leading into the evening with
zero return, apart from the frustration and anxiety levels going through the
roof.

I have read the help files on XlUp Down sideways inside out, you name it,
looked at the examples and still I am no closer to getting this last hurdle
out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks at the
History workbook/worksheet and checkout the values in Column ("A:A") and
goto (whether using XlUp or Down) and stop on the first blank cell and make
it the active cell so I can paste in the range I have selected From the
Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved please....

Many, many, many thanks way in advance.

Mick

All I need/want/desire/crave is for a handy tidbit of code that looks
at the History workbook/worksheet and checkout the values in
Column ("A:A") and goto (whether using XlUp or Down) and stop
on the first blank cell and make it the active cell so I can paste in
the range I have selected From the Master.

I have not read the entire thread, so I am going on what you wrote (quoted
above) and on the assumption that the blank cell you are looking to select
is a real blank cell and not one containing a formula that evaluates to the
empty string (""). If that is the case, then try this code...

Worksheets("History").Activate
Columns("A").SpecialCells(xlCellTypeBlanks)(1).Sel ect

I was not entirely sure what you meant by "History workbook/worksheet", so I
assumed it was the name of the worksheet in the active workbook.

Rick Rothstein (MVP - Excel)

Thank you for your reply Rick.

Actually, that is kinda where I'm finding it most difficult as any trailing
blank cells in Column A do actually have formulae in them.

I have been toying with the following code:

Which works well if I want to just highlight cells that are not "", but I am
not able to amend it to include the entire row of it selection.

The problem is that once it runs and selects all the cells not Blank, I
can't select the range for copying as excel spits it:

With ActiveSheet
LR = .Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In .Range("A5:R" & LR)
If Cell.Value < "" Then
If Rng Is Nothing Then
Set Rng = Cell
Else
Set Rng = Union(Rng, Cell)
End If
End If
Next Cell
Rng.Select
End With

TIA
Mick

Ooops!!!

Sorry Rick, confusion raining down on you right now I should imagine.

That bit of code was to a seperate one I was toying with to copy the range
from the Master file.

Although, I would like to touch base with you on that if you have time.

The problem is related though, as the current code I used was a standard
range(A5:A200"). which was fine, but when I ran the code again, it placed
the next import of data 60 rows below as, although the amount of data will
rarely extend to row 200, at the time, I was making provisions in that
event, suffice to say, when pasting to the "History" File it takes into
account that the imbedded formula is a not blank cell and stops way down the
sheet.

So to re-cap.

Essentially, using "Master" Column A:A if not blank, select all non blank
rows (Excluding those with formula).copy selection.

Windows("History.xls").Activate

Find the next available blank cell in Column A and
..PasteSpecial.Values......

Clif has been a real sport helping me by making me do the head work and
having me try and contruct it for myself in order to learn how to be self
relient, but the cup is kinda full of Access related coding and I just can't
make the dots connect.

That will enable me to move onto the next phase, which will no doubt bring
with it it's own subset of head smashing agony....LOL....

TIA
Mick

Also Rick

I am calling the GetLastRow Function to locate the next available blank cell
in "History.xls"

But...!!!

If you have a better alternative to that, I'm open to it.

Cheers

"Vacuum Sealed" wrote in message
ond.com...
What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window.
I've spent last night and most of this afternoon leading into the
evening with zero return, apart from the frustration and anxiety
levels going through the roof.

I have read the help files on XlUp Down sideways inside out, you name
it, looked at the examples and still I am no closer to getting this
last hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks
at the History workbook/worksheet and checkout the values in Column
("A:A") and goto (whether using XlUp or Down) and stop on the first
blank cell and make it the active cell so I can paste in the range I
have selected From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved
please....

Many, many, many thanks way in advance.

Mick


Hoo boy.

From your posts in other branches, I realize that your "blank" cells are
not empty (which was the assumption I was working with) but contain
formulae that are returning the ZLS (zero length string, or "").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code will
need to take Range.Areas into account)

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lStart As Long
Dim lEnd As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lStart = ca.Count ' start at the bottom
lEnd = 1

For lLoop = lStart To lEnd Step -1
If ca(lLoop).Value = "" Then
ca(lLoop).Activate
Exit For
End If
Next lLoop
If lLoop = lEnd - 1 Then
MsgBox "No blank cell found!!!", vbCritical
Stop ' error handling needed here
End If

Set ca = Nothing ' clean up


Rick, thanks for joining the thread -- I believe your contribution got
us off the hold-up!


--
Clif McIrvin

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

Oops -- I was in too big a hurry ... corrected code below.

"Clif McIrvin" wrote in message
...
"Vacuum Sealed" wrote in message
ond.com...
What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window.
I've spent last night and most of this afternoon leading into the
evening with zero return, apart from the frustration and anxiety
levels going through the roof.

I have read the help files on XlUp Down sideways inside out, you name
it, looked at the examples and still I am no closer to getting this
last hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks
at the History workbook/worksheet and checkout the values in Column
("A:A") and goto (whether using XlUp or Down) and stop on the first
blank cell and make it the active cell so I can paste in the range I
have selected From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved
please....

Many, many, many thanks way in advance.

Mick


Hoo boy.

From your posts in other branches, I realize that your "blank" cells
are not empty (which was the assumption I was working with) but
contain formulae that are returning the ZLS (zero length string, or
"").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code
will need to take Range.Areas into account)

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lStart As Long
Dim lEnd As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lStart = ca.Count ' start at the bottom
lEnd = 1

For lLoop = lStart To lEnd Step -1
If ca(lLoop).Value < "" Then
ca(lLoop + 1).Activate
Exit For
End If
Next lLoop
If lLoop = lEnd - 1 Then
ca(lLoop + 1).Activate
End If

Set ca = Nothing ' clean up



Rick, thanks for joining the thread -- I believe your contribution got
us off the hold-up!


--
Clif McIrvin

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





--
Clif McIrvin

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

Witten in haste, reviewed in haste. Re-revised code and comments
in-line.

"Clif McIrvin" wrote in message
...
"Vacuum Sealed" wrote in message
ond.com...
What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window.
I've spent last night and most of this afternoon leading into the
evening with zero return, apart from the frustration and anxiety
levels going through the roof.

I have read the help files on XlUp Down sideways inside out, you name
it, looked at the examples and still I am no closer to getting this
last hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks
at the History workbook/worksheet and checkout the values in Column
("A:A") and goto (whether using XlUp or Down) and stop on the first
blank cell and make it the active cell so I can paste in the range I
have selected From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved
please....

Many, many, many thanks way in advance.

Mick


Hoo boy.

From your posts in other branches, I realize that your "blank" cells
are not empty (which was the assumption I was working with) but
contain formulae that are returning the ZLS (zero length string, or
"").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
2. the code below does not require "blank" formula cells below the
active data. If the last formula cell is non-blank, the following row
will be chosen.
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code
will need to take Range.Areas into account)
4. This code does not test for possibly exceeding the Excel maximum row
number.

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lBottom As Long
Dim lTop As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value < "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up


Rick, thanks for joining the thread -- I believe your contribution got
us off the hold-up!


--
Clif McIrvin

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

What about this single line of code (it selects the first blank cell in
Column A...

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

If the entire row is to be selected, then use this instead/..

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).EntireRow.Select

Rick Rothstein (MVP - Excel)




"Clif McIrvin" wrote in message
...

Witten in haste, reviewed in haste. Re-revised code and comments
in-line.

"Clif McIrvin" wrote in message
...
"Vacuum Sealed" wrote in message
ond.com...
What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the window.
I've spent last night and most of this afternoon leading into the evening
with zero return, apart from the frustration and anxiety levels going
through the roof.

I have read the help files on XlUp Down sideways inside out, you name it,
looked at the examples and still I am no closer to getting this last
hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that looks at
the History workbook/worksheet and checkout the values in Column ("A:A")
and goto (whether using XlUp or Down) and stop on the first blank cell
and make it the active cell so I can paste in the range I have selected
From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved please....

Many, many, many thanks way in advance.

Mick


Hoo boy.

From your posts in other branches, I realize that your "blank" cells are
not empty (which was the assumption I was working with) but contain
formulae that are returning the ZLS (zero length string, or "").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below your
active data
2. the code below does not require "blank" formula cells below the
active data. If the last formula cell is non-blank, the following row
will be chosen.
3. there will *never* be a cell anywhere "above" the desired "first blank
cell" that does not contain a formula (if there is, the code will need to
take Range.Areas into account)
4. This code does not test for possibly exceeding the Excel maximum row
number.

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lBottom As Long
Dim lTop As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value < "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up


Rick, thanks for joining the thread -- I believe your contribution got us
off the hold-up!


--
Clif McIrvin

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

I was pretty sure there were other ways to approach this question <g

I've heard it said that when the only tool you know is a hammer, every
problem looks like a nail.

Thanks!

Clif

"Rick Rothstein" wrote in message
...
What about this single line of code (it selects the first blank cell
in Column A...

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

If the entire row is to be selected, then use this instead/..

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).EntireRow.Select

Rick Rothstein (MVP - Excel)




"Clif McIrvin" wrote in message
...

Witten in haste, reviewed in haste. Re-revised code and comments
in-line.

"Clif McIrvin" wrote in message
...
"Vacuum Sealed" wrote in message
ond.com...
What a difference a day can make..!!!!!!!!!

Hi Clif

I'm just about ready to detonate and throw my monitor out the
window. I've spent last night and most of this afternoon leading
into the evening with zero return, apart from the frustration and
anxiety levels going through the roof.

I have read the help files on XlUp Down sideways inside out, you
name it, looked at the examples and still I am no closer to getting
this last hurdle out the way.

All I need/want/desire/crave is for a handy tidbit of code that
looks at the History workbook/worksheet and checkout the values in
Column ("A:A") and goto (whether using XlUp or Down) and stop on the
first blank cell and make it the active cell so I can paste in the
range I have selected From the Master.

The rest is cake & cream.

Down on knee's anyone out there know how this can be achieved
please....

Many, many, many thanks way in advance.

Mick


Hoo boy.

From your posts in other branches, I realize that your "blank" cells
are not empty (which was the assumption I was working with) but
contain formulae that are returning the ZLS (zero length string, or
"").

Working only with Column A - Assuming:
1. there may or may not be empty cells "below" these formulae
2. there will *always* be at least one "blank" formula cell below
your active data
2. the code below does not require "blank" formula cells below the
active data. If the last formula cell is non-blank, the following row
will be chosen.
3. there will *never* be a cell anywhere "above" the desired "first
blank cell" that does not contain a formula (if there is, the code
will need to take Range.Areas into account)
4. This code does not test for possibly exceeding the Excel maximum
row
number.

This should select column A:"first blank row":

Dim ca As Range ' Column A
Dim lBottom As Long
Dim lTop As Long
Dim lLoop As Long

' set ca to all cells containing formulas in column A
Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value < "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up


Rick, thanks for joining the thread -- I believe your contribution
got us off the hold-up!


--
Clif McIrvin

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




--
Clif McIrvin

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

My humblest of apologies to both of you as I seem to have confused each of
you.

Clif

This last very nice code works well at locating a the first blank cell down
column A.

This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls" the
ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

That said...!!! It hangs on the Set CA section

And this is probably where the confusion has been added by me.

The EntireRow.Select was to select the entire row of all Cells being copied
from the "Master.xls" to "History.xls".

What was happening prior is that when I was calling the GetFirstBlankRow
Function after "History.xls" was activated, I assumed the embedded formulae
was not a blank cell and activated the cell it interpreted as being blank.

Thx again for all your efforts.

Mick

Crap, I need to proof read these things before I post em.

Repost:

My humblest of apologies to both of you as I seem to have confused each of
you.

Clif

This last very nice code works well at locating the first blank cell down
column A in "Master.xls".

This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls" the
ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

And this is probably where the confusion has been added by me.

Rick

The EntireRow.Select was to select the entire row of all Cells being copied
from the "Master.xls" to "History.xls".

So disregard the EntireRow.Select section as it turns out to be irrelavent
now that I can use the SpecialCells Set ca

What was happening prior is that when I was calling the GetFirstBlankRow
Function after "History.xls" was activated, It assumed the embedded formulae
was not a blank cell and activated the cell it interpreted as being blank
which meant it kept activating a cell 60 or so row below the last line of
populated cells.

That said...!!! It hangs on

Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

Just an after thought:

This Function worked to a degree as I explained, al-be-it the formulae was a
snag

Public Function GetLastRow() As Long


Dim ExcelLastCell As Object, Lrow As Long, lLastDataRow As Long, l As
Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

lLastDataRow = ExcelLastCell.Row

Lrow = ExcelLastCell.Row


Do While Application.CountA(ActiveSheet.Rows(Lrow)) = 0 And Lrow < 1

Lrow = Lrow - 1

Loop


lLastDataRow = Lrow



GetLastRowWithData = lLastDataRow


End Function



Thx again for all your efforts.

Mick

Comments in-line

"Vacuum Sealed" wrote in message
ond.com...
Crap, I need to proof read these things before I post em.

Hehe. Join the club<g


Repost:

My humblest of apologies to both of you as I seem to have confused
each of you.

Clif

This last very nice code works well at locating the first blank cell
down column A in "Master.xls".

Did you catch Rick's suggestion of using the Range.Find method instead?
As long as the first "blank" formula cell is the one you are looking
for, the .Find method will be much faster than the For Next loop, and I
would consider it to be much cleaner code. It's pretty obvious that Rick
has much more experience than I do, and I'm glad he joined the thread!

If there can be "blank" cells above the one you want, my code won't ever
find them because it is looking up from the bottom, where Rick's
suggested .Find is looking down from the top. I think the .Find can be
modified to look up from the bottom, by modifying the After:= and
SearchDirection:= parameters.


This worked well when I inadvertantly pasted it before the copy range
statement, but in effect it made the first blank cell of "Master.xls"
the ActiveCell which is when I realised I should have pasted it after
"History.xls" was activated.

When you are working with Active cells and Selections you need to be
*very* careful about what has the focus. After many months of dabbling
with this sort of thing, I realized that using Range objects makes it
much easier for me to explicitly define what range I am operating upon
(the $15 word is disambiguation.) (With ... End With constructs are
another way to work with explicit range objects.) Not only that, but
using range objects and temporarily turning off the user interface runs
a lot faster than using .Select and/or .Activate.

Dim myRange1 as range
Dim myRange2 as range
Dim myRange3 as range

' etc....

set myRange1 = some range of interest
set myRange2 = some other range of interest -- can be on the same or any
other sheet

then you can use myRange1.property or method, etc as self-documentation
depending on what you use as the actual variable names.

I'm not sure how important it is, but I make a practice of always
explicitly releasing objects that I instantiate in code before I exit
the procedu

set myRange1 = Nothing
etc.

And this is probably where the confusion has been added by me.

Rick

The EntireRow.Select was to select the entire row of all Cells being
copied from the "Master.xls" to "History.xls".

So disregard the EntireRow.Select section as it turns out to be
irrelavent now that I can use the SpecialCells Set ca

What was happening prior is that when I was calling the
GetFirstBlankRow Function after "History.xls" was activated, It
assumed the embedded formulae was not a blank cell and activated the
cell it interpreted as being blank which meant it kept activating a
cell 60 or so row below the last line of populated cells.

That said...!!! It hangs on

Set ca = Columns("A").SpecialCells(xlCellTypeFormulas, 23)

Have you gotten past that error? I'm unclear from your post whether you
have gotten this working or not. If this is giving you problems,
posting the actual text of the error message will be helpful.

In the line above, Columns("A") is referring to the worksheet with the
focus. From the help on Columns: "Returns a Range object that
represents all the columns on the active worksheet. If the active
document isn't a worksheet, the Columns property fails."


Just an after thought:

This Function worked to a degree as I explained, al-be-it the formulae
was a snag

Public Function GetLastRow() As Long


Dim ExcelLastCell As Object, Lrow As Long, lLastDataRow As Long,
l As Long


Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)

lLastDataRow = ExcelLastCell.Row

Lrow = ExcelLastCell.Row

I "think" that [ Lrow = lLastDataRow ] would execute faster - a simple
assignment instead of a call to a property.



Do While Application.CountA(ActiveSheet.Rows(Lrow)) = 0 And Lrow
< 1

Lrow = Lrow - 1

Loop


lLastDataRow = Lrow



GetLastRowWithData = lLastDataRow

As presented, I don't see the purpose for lLastDataRow at all - that's
just extra burden to your procedure.



End Function



Thx again for all your efforts.

Mick


Happy to help!

--
Clif McIrvin

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

Clif / Rick

Neither Set ca = codes worked, although it Compiles fine, just the execution
halts.

Here is the section of code that gives you the before and after bits the it
is dealing with:


On Error Resume Next
Windows("WowSchedMaster.xls").Activate
On Error GoTo 0


'Selects the range to be copied across

Range("A5:R200").Select
Selection.Copy


'Sets the focus back onto WowSchedHistory

On Error Resume Next
Windows("WowSchedHistory - 2011.xls").Activate
On Error GoTo 0


'Locate the first available cell in Column "A"

' Set CA to all cells containing formulas in column A

Set ca = Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select


lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value < "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up

'Paste information to first Blank Cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Thx again
Mick

The .Select at the end of your Set ca = line is causing your execution
halt. You can either [ Set (some object vairable) = xxx ] or you can
[ xxx.Select ] but you cannot do both at the same time. If you did need
to set the object and Select the range, you would do it on two lines,
thus:

Set ca = xxx
ca.Select

That being said, Rick's one-line solution is much more elegant than my
VBA loop: I "think" all you really need is this single line:

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

Also, I'm nervous about your use of On Error Resume Next without any
error handling. If the Window.Activate fails, your code won't know and
will produce erroneous results. If you're going to use Error Handling,
supply code to deal with potential failures.

Try replacing the code snippet you posted with this one:

Windows("WowSchedMaster.xls").Activate

'Selects the range to be copied across

Range("A5:R200").Select
Selection.Copy

'Sets the focus back onto WowSchedHistory

Windows("WowSchedHistory - 2011.xls").Activate

'Locate the first available cell in Column "A"

Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select

'Paste information to first Blank Cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


HTH!

Clif

"Vacuum Sealed" wrote in message
ond.com...
Clif / Rick

Neither Set ca = codes worked, although it Compiles fine, just the
execution halts.

Here is the section of code that gives you the before and after bits
the it is dealing with:


On Error Resume Next
Windows("WowSchedMaster.xls").Activate
On Error GoTo 0


'Selects the range to be copied across

Range("A5:R200").Select
Selection.Copy


'Sets the focus back onto WowSchedHistory

On Error Resume Next
Windows("WowSchedHistory - 2011.xls").Activate
On Error GoTo 0


'Locate the first available cell in Column "A"

' Set CA to all cells containing formulas in column A

Set ca = Columns("A").Find("", Cells(Rows.Count, "A"), xlValues, _
xlWhole, , xlNext).Select


lBottom = ca.Count ' start at the bottom
lTop = 1

For lLoop = lBottom To lTop Step -1
If ca(lLoop).Value < "" Then
Exit For
End If
Next lLoop
ca(lLoop + 1).Activate

Set ca = Nothing ' clean up

'Paste information to first Blank Cell

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Thx again
Mick




--
Clif McIrvin

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

It's any wonder you guy's are called Gur's

Thank you so much to both Rick and yourself Clif.

She works sweet as.

Thx again for all your patience and guidance.

Cheers
Mick.

"Vacuum Sealed" wrote in message
ond.com...
It's any wonder you guy's are called Gur's

Thank you so much to both Rick and yourself Clif.

She works sweet as.

Thx again for all your patience and guidance.

Cheers
Mick.




Glad you got it working!! Happy to help.

And, once again, Thanks to Rick for stepping in an teaching me, too!

--
Clif McIrvin

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