VBA Codes for file open

[vinwin06]

Hi All,

I have a macro coding which help me to get the values from different workbook. i have pasted the code below;

Sub start()

Dim Str, Response, Checkxls, fname As String
Dim wkbDB As Workbook, fileToOpen As Variant, f As Integer
ThisWorkbook.Activate
wkbName = ActiveWorkbook.Name
Set wkbDB = ActiveWorkbook
openfile:
fileToOpen = Application.GetOpenFilename(, , "Select the latest downloaded 'Conceptos de Facturación MKA.CSV' file", , True)
If TypeName(fileToOpen) = "Boolean" Then Exit Sub
For f = 1 To UBound(fileToOpen)
If Right(fileToOpen(f), 4) <> ".xls" Then
Checkxls = MsgBox("The file being loaded is not a CSV file! Please try again", vbRetryCancel, "Error!")
If Checkxls = vbRetry Then
GoTo openfile
Else
Exit Sub
End If
Else
If fileToOpen(f) <> False Then

parts = Split(fileToOpen(f), "\")
strFilename = parts(UBound(parts))

Workbooks.Open fileToOpen(f)
ActiveWorkbook.RunAutoMacros xlAutoOpen

'----------

Sheets("A1 PO Non-compliance").Select
Range("B6").Select

Range("B6:B44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Sheets("A1 DS").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B3").Select

Range("B3:AN3").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("c6").Select

Range("c6:c44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B21").Select

Range("B21:AN21").Select
Selection.Copy
Range("B20").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("d6").Select

Range("d6:d44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B38").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B39").Select

Range("B39:AN39").Select
Selection.Copy
Range("B38").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("e6").Select

Range("e6:e44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B74").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B75").Select

Range("B75:AN75").Select
Selection.Copy
Range("B74").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("f6").Select

Range("f6:f44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B93").Select

Range("B93:AN93").Select
Selection.Copy
Range("B92").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Sheets("A1 PO Non-compliance").Select
Range("g6").Select

Range("g6:g44").Select
Selection.Copy
Windows("2011-07 DS Dashboard1.xlsm").Activate
Range("B110").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("B111").Select

Range("B111:AN111").Select
Selection.Copy
Range("B110").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
'----------

End If
End If
Next f

End Sub


I have a clarification in only two codes mentioned below;

Windows("For July'11 DS Group Dashboard L1-2-3.xls").Activate
Windows("2011-07 DS Dashboard1.xlsm").Activate

The problem for me his the file name is different for each month. So what i need to use it for specifying file need to be activate. This is the macro for the file will open with browse option after that from the open file value has to taken and pasted in the macro running file.

Can anyone help me on this.

 

[vinwin06]

Cell B2 = "B2B-India"
Source value = "B2B-India Total"

If you see above in my source value there is a space between B2B-INdia and Total. Actually that syntax is wrong . Because i have total in source value only.

 

[jbeaucaire]

I guess I don't understand what you're asking, the formula looks right to me.

 

[vinwin06]

My macro workbook i have value like "B2B-India", so in my cell B1 ="B2B-India"

My source file have value like "B2B-India Total" so my table array have the value "B2B-India Total"

How i can get the value based on the "B2B-India" from the source value "B2B-India Total".

R[-1]C = "B2B-India"
Z1 Cost to cut PO'!C1:C13 = "B2B-India Total"

So i am missing "total" from the look up value. I think u get it now.

 

[jbeaucaire]

Pretty sure I showed how to add that in post #20

 

[vinwin06]

If i used the code in post 20 then i am getting compile error "expected end of the statement"

 

[vinwin06]

The error highlighted in this " &" codes only.

 

[vinwin06]

HI MVP,

this code mentioned below works fine for me

.FormulaR1C1 = "=VLOOKUP(R[-1]C&"" Total"",'[" & fName & "]Z1 Cost to cut PO'!C1:C13,12,0)"

thanks for your help. I tried with so many codes but finally this one works for me.