Help Needed, May have bitten off more than I can chew

[Vacuum Sealed]

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

 

[Bob Phillips]

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

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

 

[Vacuum Sealed]

Thank you so much Bob.

Steps 1 & 2. Done.

 

[Vacuum Sealed]

Sorry Bob

Forgot to mention that I'm using 2003.

Cheers

 

[Bob Phillips]

Okay, a couple more

Steps 3 & 4

On Error Resume Next
Set ws =Worksheets(Worksheets("Master").Range("E2").Value2)
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

Thank you so much Bob.

Steps 1 & 2. Done.

 

[Vacuum Sealed]

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

 

[Vacuum Sealed]

Hi Bob

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

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

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

 

[Clif McIrvin]

Newcomer to this thread.... comments inline.

Hi Bob

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

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

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").Value2)
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 = ..." ?

 

[Vacuum Sealed]

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

 

[Clif McIrvin]

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.

 

[Vacuum Sealed]

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

 

[Clif McIrvin]

See comments in-line...

I appreciate you putting this all together in one post.

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!

 

[Vacuum Sealed]

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

 

[Clif McIrvin]

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>

 

[Vacuum Sealed]

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

 

[Vacuum Sealed]

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

 

[Clif McIrvin]

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).EntireRow.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

 

[Vacuum Sealed]

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

 

[Clif McIrvin]

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"
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).EntireRow. _
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>

 

[Vacuum Sealed]

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