Need to change a recorded macro/use wildcards

**Wheelie686** · 08-16-2012, 04:03 PM

Hello,

I have referenced an Excel 2007 spreadsheet called Consolidated 2012-08-18 to 2012-08-24 V2 but I just realized that the name of the sheet I need to reference isn't always going to be the same. It will however start with Consolidated 201x-xx-x to 201x-x-x where the X's are the characters that will change based on the date of the week as well as it could have a version number at the end or it could be blank at the end.

Am I able to step into the macro and replace the characters that will change with wildcards? If so, could somebody give me a little explanation as to how? I do not fully understand writing macros, I usually just record them.

Thanks!

**Tinbendr** · 08-16-2012, 08:21 PM

Originally Posted by Wheelie686

Hello,

I have referenced an Excel 2007 spreadsheet ...Am I able to step into the macro

Your question is just a little vague.

If you are manipulating the data ON the sheet you're talking about changing, then just use ActiveSheet. If this page is indeed a 'reference' page, then we'll need to see more code.

**Wheelie686** · 08-16-2012, 09:14 PM

I have two spreadsheets open. The first one is called Consolidated 2012-08-18 to 2012-08-24 V2 and the 2nd spreadsheet reads from that spreadsheet and pulls specific information. The problem I have is the macro I created reads the exact title and what I need it to do is still be able to read it even if I change the name of the first spreadsheet. The title is manually changed depending on the date.

The following is a copy of a macro I call "CHSI"

Sub CHSI()
'
' CHSI Macro
'

'
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    Sheets("CHSI").Select
    ActiveWindow.SmallScroll Down:=-315
    Range("F2:F39").Select
    ActiveWindow.SmallScroll Down:=18
    Range("F2:F49").Select
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=18
    Range("F51:F84").Select
    ActiveWindow.SmallScroll Down:=18
    Range("F51:F98").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("F100:F126").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F100:F142").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F100:F147").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=12
    Range("F149:F179").Select
    ActiveWindow.SmallScroll Down:=12
    Range("F149:F186").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F149:F196").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("F198:F223").Select
    ActiveWindow.SmallScroll Down:=18
    Range("F198:F238").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F198:F245").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("F247:F277").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F247:F292").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F247:F294").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    ActiveWindow.SmallScroll Down:=15
    Range("F296:F329").Select
    ActiveWindow.SmallScroll Down:=15
    Range("F296:F343").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A2").Select
    Windows("Consolidated 2012-08-18 to 2012-08-24 V2.xls").Activate
    Range("A2").Select
    Sheets("Consolidated").Select
    Range("A2").Select
    Windows("Forecast - 20xx-xx-xx.xlsm").Activate
End Sub

**Cutter** · 08-16-2012, 10:14 PM

@ Wheelie686

Welcome to the forum.

Please notice that code tags have been added to your post(s). The forum rules require them so please keep that in mind and add them yourself whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.

**Tinbendr** · 08-17-2012, 03:50 AM

Try this.

Put this code in the 'template' workbook in a standard module.

When you run it, it will ask for 'source file'. This is the file it will copy FROM.

Sub CHSI()
'
' CHSI Macro
Dim WBSrc As Workbook
Dim SrcFN As String
Dim WS As Worksheet
Dim WSDest As Worksheet

    'Set destination sheet as required.
    Set WSDest = Workbooks("Forecast - 20xx-xx-xx.xlsm").Sheets(1)

    'Displays open file dialog to get filename.
    SrcFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls*," _
            , FilterIndex:=1, Title:="Open Excel file", MultiSelect:=False)

    'If filename is not empty
    If SrcFN <> False Then

        'Open Workbook
        Set WBSrc = Workbooks.Open(SrcFN)

        'Set the worksheet.
        Set WS = WBSrc.Worksheets("CHSI")

        'Turn screen updating off.
        Application.ScreenUpdating = False

        'Copy away.
        With WS
            .Range("F2:F49").Copy WSDest.Range("A2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F51:F84").Copy WSDest.Range("B2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F100:F147").Copy WSDest.Range("C2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F149:F196").Copy WSDest.Range("D2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F198:F245").Copy WSDest.Range("E2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F247:F294").Copy WSDest.Range("F2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

            .Range("F296:F343").Copy WSDest.Range("G2").PasteSpecial(Paste:=xlPasteValues, _
                Operation:=xlNone, SkipBlanks:=False, Transpose:=False)

        End With
    End If

    'Turn screen updating on.
    Application.ScreenUpdating = True

End Sub

**Wheelie686** · 08-17-2012, 10:35 AM

When I do that it gives me a Debug error and when I open VB it says If SrcFN <> False Then

**Tinbendr** · 08-17-2012, 11:37 AM

Try

if SrcFN <> "False" Then

**Wheelie686** · 08-17-2012, 12:48 PM

That just caused another debug error. Can I not just replace the characters that will change with some sort of wildcard character?

Need to change a recorded macro/use wildcards

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Re: Need to change a recorded macro/use wildcards

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions