Tuesday, 22 January 2013

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

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

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
ThisWorkbook.Sheets("Mydata").Cells(1, X).PasteSpecial Paste:=xlPasteValues
End If
Next Z

End If

Next X


MsgBox "DATA Succesfull Copied From " & " " & TargetFileName & vbNewLine _
& "You Can Select Next File", vbInformation, "Success !!!"
Exit Sub

MsgBox Err.Description
End Sub

Thanks, Tamatam

No comments:

Post a Comment

Hi User, Thank You for Visiting My Blog. Please Post Your Feedback/Comments/Query.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts