PDA

View Full Version : [SOLVED] VBA Copy data based on name & paste into workbook w/ named tab



msquared99
04-09-2013, 08:01 AM
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
04-09-2013, 11:59 PM
sample files would be helpful

msquared99
04-10-2013, 07:00 AM
Attached is file 1.

msquared99
04-10-2013, 07:06 AM
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
04-10-2013, 07:09 AM
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
04-12-2013, 08:19 AM
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
04-12-2013, 11:30 AM
Workbooks.Open FileName:="C:\Testing\Sample Template.xlsx"
where can I find this file ?

msquared99
04-12-2013, 01:12 PM
Persistence paysoff sometimes. I finally got it to work!

patel
04-12-2013, 11:59 PM
A thread marked as SOLVED must contain the solution, the goal of SOLVED is This Thread would help other users