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.

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

Sorry Bob

Forgot to mention that I'm using 2003.

Cheers

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