Results 1 to 1 of 1

Thread: Get data from another workbook and paste to master template based on criteria

  1. #1

    Get data from another workbook and paste to master template based on criteria



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi XLGuru,

    I am wondering anyone can help me to modify little bit with the vba below. I want to create a vba that can do flexible pick up from different work by using application.getopenfilename rather than i have to put the directory in another worksheet to pull up the data.

    Could anyone advise?


    Application.GetOpenFilename("Excel-files,*.xls", _
    1,"Select One File To Open",,False)

    Code:
    Public Sub GetData()
    Dim Source As String
    Source = Dir(Sheet1.Range("J1").Value) '
    If Source = "" Then
    MsgBox "Source file doest not exist/moved!", vbExclamation, "Source File Status"
    Exit Sub
    End If
    Application.ScreenUpdating = False
    Dim mydata As String
    Dim strFileName As String
    Dim strFile As String
    strFileName = Sheet1.Range("J1").Value
    strFile = Dir(strFileName)
    strFileName = Replace(strFileName, strFile, "")
    mydata = "=" & "'" & strFileName & "[" & strFile & "]" & "RawData" & "'" & "!A1:AT10000"
    With Sheet2.Range("A1:AT10000")
    .Formula = mydata
    .Value = .Value
    End With
    Sheet2.Columns("A:AT").AutoFit
    ThisWorkbook.Worksheets("Result").Range("A1:AT10000").Replace What:="0", Replacement:="", LookAt:=xlWhole
    Columns(1).EntireColumn.Delete
    Application.ScreenUpdating = True
    MsgBox "Done", vbInformation, "Done"
    End Sub
    Attached Files Attached Files

Posting Permissions

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