Probably a really easy question...

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Quick question, I want to reference a workbook that i mentioned quite a bit in my macro code. However, I want to be able to change this workbook by just changing one line of code rather than having to go into my rather long code to change it every time.

Here's what I have so far:

Dim MyFile As Workbook

Set MyFile = ActiveWorkbook

Dim MyTarget As Workbook

Set MyTarget = windows("week 28 O.xls")

Then later in the code I have it reference MyTarget and MyFile.

MyFile opens the current workbook that I am creating and I was hoping that I could put MyTarget.Activate into every instance where it occurs and it would reference week 28 o.xls. (originally when I recorded this it had "week 28 O.xls".activate) So what I figured is that rather than having to go in and change the 28 to a 29 in every instance in the code (about 25 of them) I would just set MyTarget = ___ that way if I want to change to week 29 in all those instances I just have to change the 28 to a 29 in the set MyTarget line and then all the references to my target would apply to the new week 29.

However I'm getting an error at the Set MyTarget = windows("week 28 O.xls") line.

I feel like this is probably an easy fix. Please ask any and all questions so I can help clarify.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
Hi

There is an inconsistency here:

Code:
Dim MyTarget As Workbook

Set MyTarget = windows("week 28 O.xls")
You declare MyTarget as a workbook and then try to assign it a window??

Please try:

Code:
Dim MyTarget As Workbook

Set MyTarget = Workbooks("week 28 O.xls")
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
I put this in at the beginning of my code

Dim MyFile As Workbook

Set MyFile = ActiveWorkbook

Dim MyTarget As Workbook
Set MyTarget = Workbook("Week 28 0.xls")

and it came up with an error at the same set my target = workbook line. I also tried replacing Week 28 O.xls with the file path and that did not work either.

Thanks for your suggestion!
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
Hi Again

Did you forget the s in the Workbooks Collection or was it just a typo in the post?

Set MyTarget = Workbooks("Week 28 0.xls")
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,599
Office Version
365
Platform
Windows
Is the workbook actually open?:)
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Sorry about that, it was a typo. Here is a snipit of the code

Dim MyFile As Workbook
Set MyFile = ActiveWorkbook

Dim MyTarget As Workbook
Set MyTarget = Workbooks("Week 28 O.xls")

MyTarget.Activate
Range("D190").Select
ActiveWindow.SmallScroll Down:=-30
Range("A160:E160").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
MyFile.Activate
ActiveSheet.Paste
Sheets("547").Select
MyTarget.Activate
Sheets("547").Select
Range("E208").Select
ActiveWindow.SmallScroll Down:=-27
Range("A160:E160").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
MyFile.Activate
Range("A3").Select
ActiveSheet.Paste

and it continues on in this way basically copying information from one workbook and pasting into the correct place in another workbook.

Thanks for all your help
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
I'm sorry but I cannot reproduce your error. The only other thing that comes to my mind is that the workbook name is misspelled, like the digit "0" for the letter "O" or characters that are not visible and you think they are spaces.

Do this simple test.

Code:
Sub Test()
Dim lWbk As Long
 
For lWbk = 1 To Workbooks.Count
   MsgBox Workbooks(lWbk).Name & ", Ind:" & lWbk
Next lWbk
End Sub
And see if the "Week 28 O.xls" workbook appears in the list of opened workbooks and under which index.
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
It popped up and said

Week 28 O.xls, Ind:2

and when I just tried to run the macro again I got this error:

Compile error:

Sub or Function not defined.
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,778
Ok. I did not understand the part :"and when I just tried to run the macro again I got this error: ...", what macro?

Anyway, please run this, to check the name of the workbook:

Code:
Sub NameCodes()
Dim sWbk As String, sCodes As String
Dim l As Long
 
sWbk = Workbooks(2).Name
sCodes = sWbk
 
For l = 1 To Len(sWbk)
    sCodes = sCodes & vbCrLf & Mid(sWbk, l, 1) & " : " & AscW(Mid(sWbk, l, 1))
Next l
MsgBox sCodes
End Sub
This is what should be the output on the message box:

Code:
Week 28 O.xls
W : 87
e : 101
e : 101
k : 107
  : 32
2 : 50
8 : 56
  : 32
O : 79
. : 46
x : 120
l : 108
s : 115
 

Watch MrExcel Video

Forum statistics

Threads
1,101,800
Messages
5,482,964
Members
407,371
Latest member
gdjenkins80

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top