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.
Bookmarks