Copy Specific Columns using VBA

[Glasgowsmile]

Good Afternoon,

In the following code I'm importing data from another worksheet. As you can see it's currently importing range A1:T40. I want to copy a few specific columns from the report instead. It's about 8 columns, data going down to row 40. How would I do that?

Sub ImportDatafromotherworksheet()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Sheets("Orders").Range("A1:T40").Copy wkbCrntWorkBook.Sheets("Sheet1").Range("A1")
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
   End With
End Sub

 

[Fluff]

Something like

With Sheets("Orders")
   Intersect(.Range("A1:T40"), .Range("A:A, C:C, H:H")).Copy SwkbCrntWorkBook.Sheets("Sheet1").Range("A1")
End With

 

[Glasgowsmile]

Where should I put that code? I put it here, but I get an error.

Sub ImportDatafromotherworksheet()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
   With Sheets("Orders")
   Intersect(.Range("A1:T40"), .Range("A:A, C:C, H:H")).Copy SwkbCrntWorkBook.Sheets("Sheet1").Range("A1")
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Sheets("Orders").Range("A1:T40").Copy wkbCrntWorkBook.Sheets("Sheet1").Range("A1")
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
   End With
   End With
End Sub

 

[Fluff]

Try

Sub ImportDatafromotherworksheet()
Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Intersect(Sheets("Orders").Range("A1:T40"), Sheets("Orders").Range("A:A, C:C, H:H")).Copy _
            wkbCrntWorkBook.Sheets("Sheet1").Range("A1")
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
   End With
End Sub