PDA

View Full Version : [SOLVED] Copy to OPEN Workbook, not named...



thomaspatton
07-12-2007, 03:55 PM
I'm working on a project for work and I recorded a good macro. Don't hate me for recording:devil2:

The macro is in the current month's workbook and copies 7 pages in order from the current months workbook to a blank reporting workbook. The only problem I'm getting with this is having to go in and manually change the recorded macro to read the current month's workbook, since the macro was recorded on a blank workbook.

Here's my question:
Can I place a variable in the macro to read the name of the current month's workbook without having to go in and change it? That way a clean book can be created every month without changing any macro code. I need this to be dummy proof... ya know, government work.

Here's my [recorded] code:
I highlighted the parts I need to be "variables", meaning it should read the name of the current month workbook that the macro is contained in.


Sub Compile_Report()
' Compile_Report Macro
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Range("A4:AK63").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("F4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll Down:=-36
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Master Roster").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:C2").Select
Windows("Blank Student Workbook.xls").Activate
Range("A4:AK63").Select
ActiveWindow.SmallScroll Down:=-48
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("HazMat Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("HazMat Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("EPC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("EPC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("ALC Report").Select
Cells.Select
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("ALC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:L1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
Windows("Blank Student Workbook.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Application.CutCopyMode = False
Selection.Copy
Windows("Blank Report Template.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Data").Select
End Sub


Any help that anyone can offer will be much appreciated. Even if it's just a "That can't be done n00b, stop recording macroes." post.

lucas
07-12-2007, 04:18 PM
Couple of questions.

Blank Student Workbook.xls

will the above be a month like this: July Student Workbook.xls?

Second, why not put the code in the Blank student workbook and copy just the sheets...not the ranges to a new workbook and name it report..etc.?

lucas
07-12-2007, 04:29 PM
I bet this does a lot of blinking while it runs...

thomaspatton
07-13-2007, 04:02 AM
Lol, yes it does a lot of blinking, but it's good enough for government work >.o

The "Blank Student Workbook.xls" is named after the current class month, as in "9-07.xls". I can't put it in the "Blank Report.xls" and copy the sheets because the sheets are full of formulas that update off of several of the pages in the current months workbook. When you just copy the pages the formulas don't get all the info they need because the actual current month workbook has about 16 pages. All I need is for it to copy the values for a printable/sendable report.

What happens is this. Whoever is doing the current month's workbook opens up the "Blank Student Workbook.xls" and names it after the month needed, like "9-07.xls", and saves it because the "Blank" is write-protected so they can't erase my stuff. The macro is stored in the current class book so that when they open up the "Blank Report Template.xls" they can hit the button on "9-07.xls" and it will copy what I need. the only problem is that the macro is set to pull pages from "Blank Student Workbook.xls" and not from whatever book is actually open.

Bob Phillips
07-13-2007, 04:50 AM
A few suggestions.

Address the variable workbook likeso


Dim oWB as Workbook
Set oWB = Workbooks("Student Workbook " & Format(Date,"mmm'))'
oWB.Activate

Cut down on the selecting


Windows("Blank Student Workbook.xls").Activate
Sheets("Alpha Roster").Select
Range("A4:AK63").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("F4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

can be written as


oWB.Activate
With Sheets("Alpha Roster").Sort Key1:=.Range("D4"), _
Order1:=xlAscending, _
Key2:=.Range("E4"), _
Order2:=xlAscending, _
Key3:=.Range("F4"), _
Order3:=xlAscending, _
Header:= xlGuess



Cells.Select
Selection.Copy

by


Cells.Copy


etc.

and remove all of the scrolling statements.

thomaspatton
07-13-2007, 07:26 AM
A few suggestions.

Address the variable workbook likeso


Dim oWB as Workbook
Set oWB = Workbooks("Student Workbook " & Format(Date,"mmm'))'
oWB.Activate


Thanks for the help on cleaning up the code abit, but this part I'm not sure I'm undertasnding completely.

My current class workbooks are named as "9-07.xls", "10-07.xls", and so on, but the names are given manually by the user when they save the workbook. With your code above, do I need to name my workbooks something different for the variable to work? and is the Format(Date,"mmm') portion of the code setting a date for the workbook name automatically?

I'm sorry if I'm seeming dense, but to me it looks like I would have to name my workbooks something like "Student Workbook Jun.xls" for this to work correctly. If that's the case, is it possible to set a variable that just reads the name that's already given to the workbook? Something like:


Set oWB = Workbooks("place workbook name variable here")'

If not, like I said, thanks for the efforts so far!

Bob Phillips
07-13-2007, 07:32 AM
It wasn't meant to be a full solution, just an indication for you to work on. As your code doesn't open or create the student workbook that I can see, I couldn't be prescriptive.

If you open a template workbook, just set that variable to the workbook as you open it


Set oWB = Workbooks.Open("Blank Student Workbook.xls")

You can avoid a manual save by doing it automatically, either at the end, or immediately after opening it


oWB.SaveAs Filename:= Format(Date,"mm-yy") & ".xls"

lucas
07-13-2007, 07:42 AM
I'm probably a little confused but if you have copied a workbook with the code and renamed it and you wish to refer to the same workbook:


oWB = ThisWorkbook.Name & ".xls"

thomaspatton
07-13-2007, 08:16 AM
I'm probably a little confused but if you have copied a workbook with the code and renamed it and you wish to refer to the same workbook:


oWB = ThisWorkbook.Name & ".xls"


This looks a little more like what I was looking for since I want the macro to automatically recognize the name of the new workbook. However, I tried it and got a "Compile error: Type mismatch" that highlighted the "&" of the code.

This is what is at the top of my code right now:


Sub Compile_Report()
' Compile_Report Macro
' Macro recorded by ---
Dim oWB As Workbook
Set oWB = ThisWorkbook.Name & ".xls"
oWB.Activate
Cells.Copy

lucas
07-13-2007, 08:27 AM
Dim oWB as string and try it.

thomaspatton
07-13-2007, 08:36 AM
Now I'm getting a "Compile error: Object required" and it highlites the "oWB=" portion...


Dim oWB As String
Set oWB = ThisWorkbook.Name & ".xls"
oWB.Activate

OdiN
07-13-2007, 08:41 AM
To stop the blinking as well as speed up the process do this:

At the beginning of your code put:

Application.ScreenUpdating = False

At the end of your code put:

Application.ScreenUpdating = True

This will speed things up because Excel won't have to show everything being done on the screen, and it's just easier on the eyes.

lucas
07-13-2007, 08:43 AM
Try to run this from your workbook:
Sub a()
Dim oWB As String
oWB = ThisWorkbook.Name & ".xls"
MsgBox oWB
End Sub

Bob Phillips
07-13-2007, 08:49 AM
This looks a little more like what I was looking for since I want the macro to automatically recognize the name of the new workbook. However, I tried it and got a "Compile error: Type mismatch" that highlighted the "&" of the code.

This is what is at the top of my code right now:


Sub Compile_Report()
' Compile_Report Macro
' Macro recorded by ---
Dim oWB As Workbook
Set oWB = ThisWorkbook.Name & ".xls"
oWB.Activate
Cells.Copy


Just use


Set oWB = ThisWorkbook

and refer in the code to oWB.Name

thomaspatton
07-13-2007, 08:52 AM
Try to run this from your workbook:

Sub a()
Dim oWB As String
oWB = ThisWorkbook.Name & ".xls"
MsgBox oWB
End Sub


I started fresh and entered it then ran the macro. The Alert Box says "test.xls.xls", so that worked.

So I tried deleting the '& ".xls"' of the original code and it still gives a Type Mismatch, but then highlites the ".Name" portion.

Would it help if I posted the files I was working with?

thomaspatton
07-13-2007, 08:56 AM
Just use


Set oWB = ThisWorkbook

and refer in the code to oWB.Name

HALLELUJAH!!!!

You guys rock! As soon as I refine the code I'll post it and leave this solved.

Thanks alot for everyones' help. My colleagues and I appreciate it immensely.

lucas
07-13-2007, 08:57 AM
type mismatch means it can't find it......has it been saved?
would it be possible for you to post your workbook?

lucas
07-13-2007, 08:59 AM
good news then....thanks to Bob for following up.

thomaspatton
07-13-2007, 09:04 AM
Here's my finished code. I know you said to clean it up xld, but when I tried with the method you gave me I get a Sytnax error and it highlights the whole rewritten portion. I'm still going to mark it solved, though, since the problem I was having is fixed. Once again, thanks to everyone who helped to get this working.


Sub Compile_Report()
' Compile_Report Macro
' Macro recorded by ---
Dim oWB As Workbook
Set oWB = ThisWorkbook
oWB.Activate
Cells.Copy
Windows("Blank Report Template.xls").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
oWB.Activate
Sheets("Alpha Roster").Select
Range("A4:AK63").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("D4"), Order1:=xlAscending, Key2:=Range("E4") _
, Order2:=xlAscending, Key3:=Range("F4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
ActiveWindow.SmallScroll Down:=-36
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Master Roster").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:C2").Select
oWB.Activate
Range("A4:AK63").Select
ActiveWindow.SmallScroll Down:=-48
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("HazMat Report").Select
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("HazMat Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
oWB.Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("EPC Report").Select
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("EPC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
oWB.Activate
Sheets("Alpha Roster").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C4"), Order1:=xlDescending, Key2:=Range("D4") _
, Order2:=xlAscending, Key3:=Range("E4"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Sheets("ALC Report").Select
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("ALC Report").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:L1").Select
oWB.Activate
Sheets("Finalized Summary").Select
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Finalized Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:B1").Select
oWB.Activate
Sheets("AMC Summary").Select
Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("AMC Summary").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1").Select
Sheets("Data").Select
End Sub

Bob Phillips
07-13-2007, 10:02 AM
This is the sort of thing that I envisioned. You can go much further, but that would mean understanding the application, which I don't.

Do double-check it though, I may have mis-transcribed some bits.


Sub Compile_Report()
Dim oWB As Workbook
Set oWB = ThisWorkbook
oWB.Activate
Cells.Copy
Windows("Blank Report Template.xls").Activate
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
oWB.Activate
With Sheets("Alpha Roster").Range("A4:AK63")
.Sort Key1:=Range("D4"), _
Order1:=xlDescending, _
Key2:=Range("E4"), _
Order2:=xlAscending, _
Key3:=Range("F4"), _
Order3:=xlAscending, _
Header:=xlGuess
End With
Sheets("Alpha Roster").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Master Roster").Select
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
oWB.Activate
Range("A4:AK63").Sort _
Key1:=Range("A4"), _
Order1:=xlDescending, _
Key2:=Range("D4"), _
Order2:=xlAscending, _
Key3:=Range("E4"), _
Order3:=xlAscending, _
Header:=xlGuess
Sheets("HazMat Report").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("HazMat Report").Select
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("A1:K1").Select
oWB.Activate
Sheets("Alpha Roster").Sort _
Key1:=Range("B4"), _
Order1:=xlDescending, _
Key2:=Range("D4"), _
Order2:=xlAscending, _
Key3:=Range("E4"), _
Order3:=xlAscending, _
Header:=xlGuess
Sheets("EPC Report").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("EPC Report").Select
Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1:K1").Select
oWB.Activate
Sheets("Alpha Roster").Select
Selection.Sort Key1:=Range("C4"), _
Order1:=xlDescending, _
Key2:=Range("D4"), _
Order2:=xlAscending, _
Key3:=Range("E4"), _
Order3:=xlAscending, _
Header:=xlGuess
Sheets("ALC Report").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("ALC Report").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
oWB.Activate
Sheets("Finalized Summary").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("Finalized Summary").Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
oWB.Activate
Sheets("AMC Summary").Cells.Copy
Windows("Blank Report Template.xls").Activate
Sheets("AMC Summary").Cells.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Data").Select
End Sub