Creating Macro to copy data from one WB & Save to new WB

[Closed]
Report
Posts
2
Registration date
Thursday January 10, 2013
Status
Member
Last seen
January 11, 2013
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I am very new to creating Macro's and need some assistance. I have a large report with data for several different campuses. I'd like to create a macro that would pull the data from each college separeately and save that data into a new workbook and save the new workbook based on the campus number.
The second column contains a campus number from 2-100.

Any suggestions would be wonderful!
Sara

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Well the first start could be if you start macro recorder, create a sheet as you want and then stop the macro. This can give you a good template to start
Posts
2
Registration date
Thursday January 10, 2013
Status
Member
Last seen
January 11, 2013

The code below was recorded; but I am getting an error:

Sub PullData()
'
' PullData Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
ActiveSheet.Range("$A$1:$S$63758").AutoFilter Field:=2, Criteria1:="2"
ActiveCell.Offset(-63764, 0).Range("A1:S3245").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\sawallac\Documents\Campus2.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
ChDir "C:\Users\sawallac\Desktop\Project Management\FAC Need Projections"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\sawallac\Desktop\Project Management\FAC Need Projections\UOPFacultycourseload.xlsm" _
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub


Any Suggestions?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
see this thread
it is same as you want , except that creates separate sheets instead of workbooks

https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions