Tuesday, 22 January 2013

How To Open a Dialog Box to Select a File and Copy Desired Columns

Excel VBA Macro To Open a Dialog Box to Select a File and Copy  Desired Columns which Match with our Workbook Columns


Sub CopyDesiredCols()
Dim TargetFile As Variant
Dim TargetFileName As Variant
Dim Ws As Worksheet
Dim X As Long
Dim Y As Long
Dim Z As Long

Y = ActiveSheet.Columns.Count

TargetFile = Application.GetOpenFilename("Excel Files, *.xls;*.xlsx;*.xlsm", , "Select Your File")

'If TargetFile = False Then Exit Sub

Workbooks.Open (TargetFile)
TargetFileName = ActiveWorkbook.Name
Workbooks(TargetFileName).Activate

For X = 2 To Y
If ThisWorkbook.Sheets("Mydata").Cells(1, X) <> "" Then

For Z = 2 To Y
If LCase(ThisWorkbook.Sheets("Mydata").Cells(1, X)) = LCase(ActiveWorkbook.Sheets("Database").Cells(1, Z)) Then
ActiveWorkbook.Sheets("Database").Cells(1, Z).EntireColumn.Select
Selection.Copy
ThisWorkbook.Sheets("Mydata").Cells(1, X).PasteSpecial Paste:=xlPasteValues
End If
Next Z

End If
Next X

Workbooks(TargetFileName).Save
Workbooks(TargetFileName).Close
MsgBox "DATA Succesfull Copied From " & " " & TargetFileName & vbNewLine _
& "You Can Select Next File", vbInformation, "Success !!!"
Exit Sub
'------------------------------------------------------------------------------------------'
TPR:
MsgBox Err.Description
End Sub

Thanks.,
T P REDDY
     [Excel Macro Man ]

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts