Change VB code by cell reference

[seahawk56]

I'm creating a few workbooks that copies information from one and pastes it in another. Since the names of these workbooks will change from day to day, I'm trying this coding that a friend showed me. It is supposed to pull what is in Row 1 Column 2 for the name of the workbook, Row 1 Column 4 is the month (name of folder to open the workbook), and Row 3 Column 2 for the name of the workbook it is pulling the data from.
I'm having problems with the stuff highlighted in red. If I manually change it then it works. I do not know what is wrong with the Month but the Plannedwork I think it does not like the ".xls" but I believe that piece has to be there to open up the spread sheet. It works in the other sections I have it but in the pathway it ends with another " it also give me an error for Expected: end of statement.

Option Explicit
Const sheetname1 = "KaizenBoard"
Dim Prodweek As String
Dim Month As String
Dim Plannedwork As String
Sub MondayKB()
'
' MondayKB Macro
' Macro recorded 12/6/2008 by whitjh1
'
'
    Prodweek = Trim(Sheets(sheetname1).Cells(1, 2).Value)
    Month = Trim(Sheets(sheetname1).Cells(1, 4).Value)
    Plannedwork = Trim(Sheets(sheetname1).Cells(3, 2).Value)
    Workbooks.Open Filename:= _
        "G:\HPO\HPO Workplanning\POS\Planned Work\Month\Plannedwork & ".xls""
    Range("BU15:BV15").Select
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("CA15:CB15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("BU22:BV22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("CA22:CB22").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("BU30:BV30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("CA30:CB30").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("BY31:BZ31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B19").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("CA31:CB31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Windows(Plannedwork & ".xls").Activate
    Range("BV33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(Plannedwork & ".xls").Activate
    Range("BX33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(Plannedwork & ".xls").Activate
    Range("BZ33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows(Prodweek & ".xls").Activate
    Range("B25").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows(Plannedwork & ".xls").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

 

[gauntletxg]

You need to isolate your variables

"G:\HPO\HPO Workplanning\POS\Planned Work\" & Month & "\" & Plannedwork & ".xls"

Also you should change your Month variable to something more unique, like MyMonth. There's already a function called Month, which can cause confusion.

 

[jindon]

seahawk56<SCRIPT type=text/javascript> vbmenu_register("postmenu_1768195", true); </SCRIPT>

Apart from yuor question.

Is there any good reason for decalring global variables ?

Option Explicit
Const sheetname1 = "KaizenBoard"
Dim Prodweek As String
Dim Month As String
Dim Plannedwork As String

 

[seahawk56]

Thanks gauntletxg,
I do not understand it but what ever it is worked and I took your advice on the MyMonth. I thought it might have something to do with being to common.

jindon
The code my friend showed me was just the Prodweek one and I just copied and pasted it in hopes that it would work. I'm trying to learn this stuff as I go, so I do not know what a global variable is. If you have any suggestions to clean it up I would appreciate it.
Thank you to both you guys for quick responses.
Seahawk56

 

[jindon]

jindon
The code my friend showed me was just the Prodweek one and I just copied and pasted it in hopes that it would work. I'm trying to learn this stuff as I go, so I do not know what a global variable is. If you have any suggestions to clean it up I would appreciate it.
Thank you to both you guys for quick responses.
Seahawk56

Ok so, no need to be global...

It is not a good idea to decalre variables as global for no reason.
You may sometime have a trouble for that.
I suggest you to declare variable within a subroutine so that it will not affect to other procedure...

Option Explicit
 
Sub MondayKB()
'
' MondayKB Macro
' Macro recorded 12/6/2008 by whitjh1
'
'
Const sheetname1 = "KaizenBoard"
Dim Prodweek As String
Dim Month As String
Dim Plannedwork As String
 
    Prodweek = Trim(Sheets(sheetname1).Cells(1, 2).Value)