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!
Bookmarks