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

Closed
sawallac Posts 2 Registration date Thursday January 10, 2013 Status Member Last seen January 11, 2013 - Jan 10, 2013 at 04:05 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 11, 2013 at 11:18 AM
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

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 10, 2013 at 05:12 PM
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
0
sawallac Posts 2 Registration date Thursday January 10, 2013 Status Member Last seen January 11, 2013
Jan 11, 2013 at 09:40 AM
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?
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 11, 2013 at 11:18 AM
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
0