dougmarkham
Active Member
- Joined
- Jul 19, 2016
- Messages
- 252
- Office Version
- 365
- Platform
- Windows
Hi folks,
I have been tasked with splitting a master worksheet into multiple worksheets i.e., splitting the data by values in a selected column.
For instance: if the selected column contained different values for 'sales person', the task would be to split sales data for each 'sales person' into a new worksheet (within the same workbook) named after the 'sales person'.
I found some VBA code for splitting into multiple workbooks and modified the code to instead split the data into multiple worksheets. Here is the code:
The idea is that you select the column (e.g., Col C) containing the variable that you're extracting by, then excel selects by the first variable in 'Column C' it comes to, extracts the rows of data with corresponding to that variable, then loops to the next variable down in 'Column C'. Then excel repeats till all the variables in that column have had the data extract completed. So if column C is selected and there are 20 sales persons, it cycles through each sales person.
Using F8 to check each stage in testing, the error occurs after Cells.Copy: specifically when excel tries to paste into the new sheet i.e.,
After pasting, I get the following error messages:
1) Microsoft excel error message - Excel cannot complete this task with available resources. Choose less data or close other applications.
2) VBA error message - Run-time error '1004' PasteSpecial method of Range class failed.
I closed all other applications and shortened the data set but still get the error message.
Now, I looked into this using Google search and one fellow who had a similar experience said:
Would anybody be willing to help me fix this code if possible, or will anybody suggest an alternative?
Kind regards,
Doug.
I have been tasked with splitting a master worksheet into multiple worksheets i.e., splitting the data by values in a selected column.
For instance: if the selected column contained different values for 'sales person', the task would be to split sales data for each 'sales person' into a new worksheet (within the same workbook) named after the 'sales person'.
I found some VBA code for splitting into multiple workbooks and modified the code to instead split the data into multiple worksheets. Here is the code:
Code:
[COLOR=#0000cd]Sub[/COLOR] Split()
[COLOR=#0000cd]Dim[/COLOR] example [COLOR=#0000cd]As String[/COLOR]
[COLOR=#0000cd]Dim [/COLOR]data [COLOR=#0000cd]As String[/COLOR]
example = ActiveWorkbook.Name
data = ActiveSheet.Name
vColumn = InputBox("Please indicate which column (i.e. A, B, C, …), you would like to split by", "Column selection")
Columns(vColumn).Copy
Sheets.Add
ActiveSheet.Name = "_Summary"
Range("A1").PasteSpecial
Columns("A").RemoveDuplicates Columns:=1, Header:=xlYes
vCounter = Range("A" & Rows.Count).End(xlUp).Row
[COLOR=#0000cd]For[/COLOR] i = 2 [COLOR=#0000cd]To[/COLOR] vCounter
vfilter = Sheets("_Summary").Cells(i, 1)
Sheets(data).Activate
ActiveSheet.Columns.AutoFilter field:=Columns(vColumn).Column, Criteria1:=vfilter
Cells.Copy
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
[COLOR=#b22222] Range("A1").PasteSpecial[/COLOR]
[COLOR=#0000cd] If[/COLOR] vfilter <> "" [COLOR=#0000cd]Then[/COLOR]
ActiveSheet.Name = vfilter
[COLOR=#0000cd] Else[/COLOR]
Application.DisplayAlerts = [COLOR=#0000cd]False[/COLOR]
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = [COLOR=#0000cd]True[/COLOR]
[COLOR=#0000cd] End If[/COLOR]
'Activate Workbook
Workbooks(example).Activate
[COLOR=#0000cd]Next i[/COLOR]
Sheets("_Summary").Delete
[COLOR=#0000cd]End Sub[/COLOR]The idea is that you select the column (e.g., Col C) containing the variable that you're extracting by, then excel selects by the first variable in 'Column C' it comes to, extracts the rows of data with corresponding to that variable, then loops to the next variable down in 'Column C'. Then excel repeats till all the variables in that column have had the data extract completed. So if column C is selected and there are 20 sales persons, it cycles through each sales person.
Using F8 to check each stage in testing, the error occurs after Cells.Copy: specifically when excel tries to paste into the new sheet i.e.,
Code:
Cells.Copy
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
[COLOR=#ff0000]Range("A1").PasteSpecial[/COLOR]After pasting, I get the following error messages:
1) Microsoft excel error message - Excel cannot complete this task with available resources. Choose less data or close other applications.
2) VBA error message - Run-time error '1004' PasteSpecial method of Range class failed.
I closed all other applications and shortened the data set but still get the error message.
Now, I looked into this using Google search and one fellow who had a similar experience said:
Would anybody be willing to help me fix this code if possible, or will anybody suggest an alternative?
Kind regards,
Doug.
