Hello,
I am running into a problem with VB code provided by lasw10. However, his email states he is out of the office until Monday.
Can someone help me tinker with the code? It is supposed to take data from a single sheet and split it up by month into another book that has a sheet for each month. What is happening is only 3 months are created in the new book and each month has a line item from the next month at the bottom. If you would like to see the data I am trying to manipulate, I have included a copy of part of the sheet in another post titled "Linking data from 1 sheet to several sheets in another book?"
Here is the code, which was provided by Lasw10 (Modified slightly by me):
Sub SEPARATE_DATA()
Dim n As Integer
Dim inc As Integer
Application.ScreenUpdating = False
mainfile = "Sandy1.xls"
Workbooks.Open ("C:\Luke Work\Mr_Excel\Sandy2.xls")
monthlyfile = ActiveWorkbook.Name
Windows(mainfile).Activate
n = 2
inc = 0
Do Until Cells(n, 11) = ""
'Determine Sheet Name
SNAME = Cells(n, 11)
Do Until Cells(n, 11) <> Cells(n + inc + 1, 11)
inc = inc + 1
Loop
Windows(monthlyfile).Activate
On Error GoTo ErrorHandler
100
Sheets(SNAME).Select
'Enter Column Headings
Cells(1, 1) = "SAS First"
Cells(1, 2) = "SAS Last"
Cells(1, 3) = "Contract Number"
Cells(1, 4) = "Current Billing Freq"
Cells(1, 5) = "Price"
Cells(1, 6) = "Effective Month"
Cells(1, 7) = "Escalation Code"
Cells(1, 8) = "SpecEsc%"
Cells(1, 9) = "Esc. $"
Cells(1, 10) = "Revised Price"
'Select Copy Range in Columns A & B paste to Column A & B in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 1), Cells(n + inc, 2)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 1).PasteSpecial xlPasteValues
Cells(2, 1).PasteSpecial xlPasteFormats
'Select Copy Range in Columns F & G paste to Column C & D in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 7), Cells(n + inc, 8)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 3).PasteSpecial xlPasteValues
Cells(2, 3).PasteSpecial xlPasteFormats
'Select Copy Range in Columns J to O paste to Column E onwards in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 10), Cells(n + inc, 15)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 5).PasteSpecial xlPasteValues
Cells(2, 5).PasteSpecial xlPasteFormats
'Return to Sandy1.xls and proceed to next month batch
Windows(mainfile).Activate
If inc = 0 Then
n = n + 1
Else
n = n + inc
End If
inc = 0
Loop
'Close and Save Sandy2.xls
Windows(monthlyfile).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Sheets.Add
ActiveSheet.Name = SNAME
Resume Next
End Sub
I am running into a problem with VB code provided by lasw10. However, his email states he is out of the office until Monday.
Can someone help me tinker with the code? It is supposed to take data from a single sheet and split it up by month into another book that has a sheet for each month. What is happening is only 3 months are created in the new book and each month has a line item from the next month at the bottom. If you would like to see the data I am trying to manipulate, I have included a copy of part of the sheet in another post titled "Linking data from 1 sheet to several sheets in another book?"
Here is the code, which was provided by Lasw10 (Modified slightly by me):
Sub SEPARATE_DATA()
Dim n As Integer
Dim inc As Integer
Application.ScreenUpdating = False
mainfile = "Sandy1.xls"
Workbooks.Open ("C:\Luke Work\Mr_Excel\Sandy2.xls")
monthlyfile = ActiveWorkbook.Name
Windows(mainfile).Activate
n = 2
inc = 0
Do Until Cells(n, 11) = ""
'Determine Sheet Name
SNAME = Cells(n, 11)
Do Until Cells(n, 11) <> Cells(n + inc + 1, 11)
inc = inc + 1
Loop
Windows(monthlyfile).Activate
On Error GoTo ErrorHandler
100
Sheets(SNAME).Select
'Enter Column Headings
Cells(1, 1) = "SAS First"
Cells(1, 2) = "SAS Last"
Cells(1, 3) = "Contract Number"
Cells(1, 4) = "Current Billing Freq"
Cells(1, 5) = "Price"
Cells(1, 6) = "Effective Month"
Cells(1, 7) = "Escalation Code"
Cells(1, 8) = "SpecEsc%"
Cells(1, 9) = "Esc. $"
Cells(1, 10) = "Revised Price"
'Select Copy Range in Columns A & B paste to Column A & B in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 1), Cells(n + inc, 2)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 1).PasteSpecial xlPasteValues
Cells(2, 1).PasteSpecial xlPasteFormats
'Select Copy Range in Columns F & G paste to Column C & D in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 7), Cells(n + inc, 8)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 3).PasteSpecial xlPasteValues
Cells(2, 3).PasteSpecial xlPasteFormats
'Select Copy Range in Columns J to O paste to Column E onwards in Sandy2.xls
Windows(mainfile).Activate
Range(Cells(n, 10), Cells(n + inc, 15)).Copy
Windows(monthlyfile).Activate
Sheets(SNAME).Select
Cells(2, 5).PasteSpecial xlPasteValues
Cells(2, 5).PasteSpecial xlPasteFormats
'Return to Sandy1.xls and proceed to next month batch
Windows(mainfile).Activate
If inc = 0 Then
n = n + 1
Else
n = n + inc
End If
inc = 0
Loop
'Close and Save Sandy2.xls
Windows(monthlyfile).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
Sheets.Add
ActiveSheet.Name = SNAME
Resume Next
End Sub
