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
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