Change VB code by cell reference

seahawk56

New Member
Joined
Jun 23, 2008
Messages
30
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.

Rich (BB code):
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

gauntletxg

Well-known Member
Joined
Jul 15, 2008
Messages
636
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
seahawk56<SCRIPT type=text/javascript> vbmenu_register("postmenu_1768195", true); </SCRIPT>

Apart from yuor question.

Is there any good reason for decalring global variables ?
Rich (BB code):
Option Explicit
Const sheetname1 = "KaizenBoard"
Dim Prodweek As String
Dim Month As String
Dim Plannedwork As String
 

seahawk56

New Member
Joined
Jun 23, 2008
Messages
30
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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...
Rich (BB code):
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,364
Messages
5,444,025
Members
405,260
Latest member
Khauff

This Week's Hot Topics

Top