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

[msquared99]

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.

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

[patel]

sample files would be helpful

[msquared99]

Attached is file 1.

[msquared99]

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.

[msquared99]

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!

[msquared99]

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.

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

[patel]

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

where can I find this file ?

[msquared99]

Persistence paysoff sometimes. I finally got it to work!

[patel]

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