Copy specific columns from an inactive sheet to one excel sheet using vba

Question

My excel workbook has the following sheets:

Overview - Consists of description and a button that should export data about students in an organisation.
Sep Intake - Student details who were admitted in September
Jan Intake - Student details who were admitted in January
May Intake - Students who were admitted in May

Sheet #2, #3 and #4 consists of all the academic and personal information together. The objective is to just copy and create an excel with only columns Name, Student Id and Term in it. For this, there is a button that is placed in sheet "Overview", when clicked produces the excel sheet.

The following is my code that I use for my macro:

Private Sub CommandButton1_Click()
Dim t As Range
Dim Ws1 As Worksheet
Dim Ws As Worksheet
Dim ts As String
Dim nc As Integer
Dim i As Range
Dim j As Integer
Dim LASTROW As Integer
Dim k As Integer
Dim wb As Workbook
Dim valuessheet As Worksheet

Set Ws1 = Sheets(CStr(ActiveSheet.Range("D3").Value)) 'change control from Overview to Jan Intake
Ws1.Activate
Set Ws = ActiveSheet
Set valuesheet = ThisWorkbook.Sheets("Ref") 'Ref is a hidden sheet that consists the required column name listed in range K3 to K5

Set wb = Workbooks.Add
ts = wb.ActiveSheet.Name
nc = 2
j = 3

For Each i In valuesheet.Range("K3:K5").Cells 'Change range
    Set t = Ws.Range("A3:X50").Find(What:=i.Value, lookat:=xlWhole, LookIn:=xlValues, _
                MatchCase:=False)
    If Not t Is Nothing Then
    Columns(t.Column).Copy _
      Destination:=Sheets(ts).Cells(1, nc)
      nc = nc + 1
   Else:    MsgBox "Title Not Found"
   End If
Next i    
End Sub

I am getting the value of t correctly, but the macro keeps copying the columns of sheet Overview to the new workbook. How do I rectify that?

You have Column(t.Column).Copy. There's no worksheet given, so it's using whatever the activesheet is. You want to do something like Ws.Column(t.Column).Copy. Similarly, when you paste to just Sheets(ts), there's no workbook mentioned, so you need to make sure to add the new workbook's reference first, i.e. wb.Sheets(ts)... — BruceWayne
@BruceWayne : That worked! thank you...Can you put it as the solution, so that I can mark it. — Nimy Alex

BruceWayne BruceWayne · Accepted Answer · 2018-04-03T20:44:42

Always remember to qualify any Range() object with it's Worksheet and/or Workbook. As noted in the comments:

You have Column(t.Column).Copy. There's no worksheet given, so it's using whatever the activesheet is.

You want to do something like Ws.Column(t.Column).Copy.

Similarly, when you paste to just Sheets(ts), there's no workbook mentioned, so you need to make sure to add the new workbook's reference first, i.e. wb.Sheets(ts)...

Copy specific columns from an inactive sheet to one excel sheet using vba

1 Answers