Results 1 to 10 of 10

assign variable name to the workbook

Threaded View

yjmmay34 assign variable name to the... 08-16-2010, 11:38 PM
JBeaucaire Re: assign variable name to... 08-17-2010, 12:18 AM
yjmmay34 Re: assign variable name to... 08-17-2010, 01:15 AM
teylyn Re: assign variable name to... 08-17-2010, 12:19 AM
yjmmay34 Re: assign variable name to... 08-17-2010, 01:24 AM
teylyn Re: assign variable name to... 08-17-2010, 12:20 AM
JBeaucaire Re: assign variable name to... 08-17-2010, 01:34 AM
yjmmay34 Re: assign variable name to... 08-17-2010, 01:48 AM
teylyn Re: assign variable name to... 08-17-2010, 02:02 AM
yjmmay34 Re: assign variable name to... 08-17-2010, 02:18 AM
  1. #1
    Registered User
    Join Date
    04-12-2010
    Location
    singapore
    MS-Off Ver
    Excel 2007
    Posts
    96

    Unhappy assign variable name to the workbook

    Hi, all. please help woth this problem.
    I want to open two file at the beginning of the code, like below, and after the two files are opened, i want to activate one of the file first, and i must write the exactly same name as the workbook name, then i can activate the window. Is there any way so that assign a variable to the workbook name, and any time, i call the variable, it will know which window should be activated.
    Private Sub FileFormat()
        Workbooks.Open (Application.GetOpenFilename(, , "Please locate your file"))
        Workbooks.Open (Application.GetOpenFilename(, , "Please locate your file"))
        Windows("Pricelist").Activate
            j = 1
        Do While j <= 5
            Windows("Pricelist").Activate
            If j = 1 Then
                SheetName = "400nm & Above"
            ElseIf j = 2 Then
                SheetName = "350nm,320nm,300nm"
            ElseIf j = 3 Then
                SheetName = "250nm,220nm,200nm"
            ElseIf j = 4 Then
                SheetName = "180nm,160nm,150nm,140nm"
            ElseIf j = 5 Then
                SheetName = "130,110(200mm)"
    
            End If
            
            Sheets(j).Select
            TotalRowCount = ActiveSheet.UsedRange.Rows.Count
            i = 1
            FinalMasterRow = 0
            Do While ActiveSheet.Cells(i, 1).Value <> "End"
                Rows(i).Select
                i = i + 1
            Loop
            FinalMasterRow = i + 2
            RowCount = TotalRowCount - FinalMasterRow
            Range(Cells(FinalMasterRow, 1), Cells(TotalRowCount, "DO")).Select
            Selection.Copy
            Windows("CombinedPriceList").Activate
            Sheets("200mm").Select
            a = ActiveSheet.UsedRange.Rows.Count
            Cells(a + 1, 1).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=True, Transpose:=False
            Selection.NumberFormat = "0"
            j = j + 1
          
        Loop
    in this example, the two file name are "PriceList" and "CombinedPriceList". If i change the file name to "PriceList01" and "CombinedPriceList01", then the macro will get error. so i want to overcome this problem.
    Any help will be appreciated!
    Last edited by yjmmay34; 08-17-2010 at 01:46 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1