Results 1 to 2 of 2

copy specific data from outside data source

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,176

    copy specific data from outside data source

    I am trying to import partial data from an outside excel file. I have the code so far to open the outside Excel file but I can’t figure out how to copy only certain columns and rows of data.

    Sub Analysis()
    
    Dim i               As Long
    Dim j               As Long
    Dim n               As Long
    Dim LastRow         As Long
    Dim Prompt          As String
    Dim Title           As String
    Dim Path            As String
    Dim SourceWkb       As Workbook
    Dim DestinationWkb  As Workbook
    
        Select Case MsgBox("Are you sure you want to continue?    " & vbCrLf & _
                vbCrLf & _
                "Selecting 'Yes' will result in the     " & vbCrLf & _
                "current data being overwritten.    " _
                , vbYesNo Or vbQuestion Or vbDefaultButton1, _
                "Allocations Generator")
    
        Case vbYes
            
        Case vbNo
            Exit Sub
        End Select
    
        Set DestinationWkb = ThisWorkbook
        
        Prompt = "Select the Excel file to process."
        Path = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , Prompt)
        If Path = "False" Then
            GoTo ExitSub:
        End If
    
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        
        Workbooks.Open Filename:=Path
        
        Set SourceWkb = ActiveWorkbook
        
            'if value in cell (current row, "G") = "Chicago" then
                'copy column A to column A
                'copy column C to column C
                'copy column F to column D
                'copy column H to column F
                'copy column J to column G
            'else
                'do nothing
    
        SourceWkb.Close SaveChanges:=False
        
    ExitSub:
        
        Application.DisplayAlerts = True
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        Application.StatusBar = False
        
        Set SourceWkb = Nothing
        
    End Sub
    Once the outside file has been opened, I need to copy as follows (without deleting any columns as I need the values to compute other data once I have this part working):

    copy column A (sourceWkb) to column A (destinationWkb)
    copy column C (sourceWkb) to column C (destinationWkb)
    copy column F (sourceWkb) to column D (destinationWkb)
    copy column H (sourceWkb) to column F (destinationWkb)
    copy column J (sourceWkb) to column G (destinationWkb)

    There is also one other criteria, which is the only copy rows in which the value in column G for the sourceWkb = “Chicago”

    I have attached a copy of data file and copy of file with desired results.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1