VBA Copy data based on name & paste into workbook w/ named tab

I have a macro that allows you to select a file. The selected file contains data in columns A:Y, rows can vary. In column B there are 4 different names, such as Class A, Class B, Class C and, Class D.

There is a template that the macro opens called Class Summary Template. This template has 4 spreadsheets named Class A, Class B, Class C and , Class D.

How do I get the macro to copy each "Class" from the macro selected workbook into the matching spreadsheet in the template?

Thanks for anyone's help.

Code:

---

Sub ChooseFile()
Dim fd As FileDialog
Dim FileName As String
Dim lastRow1 As Long
Dim lastRow2 As Long
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Get the number the button chosen.
Dim FileChosen As Integer
FileChosen = fd.Show
    If FileChosen <> -1 Then
    'Didn't choose anything (clicked cancel).
    MsgBox "No file opened."
    Exit Sub
Else
    'Display name and path of file chosen.
    FileName = fd.SelectedItems(1)
    Workbooks.Open (FileName)
    FileName = Mid(FileName, InStrRev(FileName, "\") + 1, Len(FileName))
    Range("A:B,F:H,S:U,Z:AY").Select
    Selection.Delete shift:=xlToLeft
    Range("A1").Select
    'Copy data from selected workbook into Class Summary Template. Paste range begins in cell A7.
    Workbooks.Open FileName:="C:\Accounting\Class Summary Template.xlsx"
End Sub

---

sample files would be helpful

Attached is file 1.

Here file 2.

The macro is in a stand alone workbook and is activated by a click button. When the macro runs it allows you to navigate to the folder and opens the file Raw data from CVENT - sample. It assigns this workbook "FileName". The macro then opens the Survey Summary Template.

So, what I am trying to do is have the macro copy the data in column B into the Survey Summary Template that matches the tab name to the name in column B.

So far my attenpts have failed as I am just learning VBA.

Thanks for your time and help.

To be more clear. I am trying to copy the data in column B of the Raw data from CVENT - sample into the workbook "Survey Summary Template" in the appropriate tab, Team 1, Team 2 and so on.

Thanks!

Well it is not like I'm not trying and just want someone to write my code. I have been playing with code all week and have gotten nowhere. Here is what I have so far. I made a notation above the For Each loop to explain what I am trying to do.

Code:

---

Sub ChooseFile()
Dim fd As FileDialog
Dim FileName As String
Dim lastRow1 As Long
Dim rcell As Range
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Get the number the button chosen.
Dim FileChosen As Integer
FileChosen = fd.Show
If FileChosen <> -1 Then
    'Didn't choose anything (clicked cancel).
    MsgBox "No file opened."
    Exit Sub
Else
    'Display name and path of file chosen.
    FileName = fd.SelectedItems(1)
    Workbooks.Open (FileName)
    FileName = Mid(FileName, InStrRev(FileName, "\") + 1, Len(FileName))
    Workbooks.Open FileName:="C:\Testing\Sample Template.xlsx"
    Workbooks(FileName).Activate
    lastRow1 = Range("B" & Rows.Count).End(xlUp).Row
    'Want to look for each team, there are teams 1 thru 4 in column B of FileName, data is in columns A2:Y? and paste each team into
    'the appropraite tabs in Sample Template workbook.
    For Each rcell In Range("B2:B" & lastRow1)
        If rcell.Value = "Team 2" Then
            Workbooks(FileName).Range(Cells(rcell.Row, 1), Cells(recll.Row, 25)).Copy
            Workbooks("Sample Template.xlsx").Worksheets("Team 2").Range("A2").PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
        End If
    Next rcell
End If
End Sub

---

Code:

---

Workbooks.Open FileName:="C:\Testing\Sample Template.xlsx"

---

where can I find this file ?

Persistence paysoff sometimes. I finally got it to work!

A thread marked as SOLVED must contain the solution, the goal of SOLVED is This Thread would help other users