Data Sort and Paste

[Quagmire]

Hello everyone!

I've been reading the forum and I was unable to find what I needed so I'm trying to see if I can get a little help. I may have missed the post that may correlate to this question, if so I apologize.

I created a template that the person enters info on the "main" page and it goes to each individual page. (i.e info for john entered on the main page would go to the sheet labeled john). I've added a new macro that allows the user to add names and sorts them to be in alphabetical order and the information updates and follows on the individual pages. There are 2 sections on the individual pages that have to be entered manually on each page and therefore do not move with the rest of the info. I have another macro that takes all of that info on each page and copies all of it to another individual page. See code below. The problem is that I want it to sort it out by name and copy back to the approriate page. Unfortunately, I am unable to post the template or send it out due to that I utilize at my job along with other team leaders and is considered "confidential". I know my description is kinda vague, but can anyone point me in the right direction. Here is what i got so far:

Sub Datasave()

Application.ScreenUpdating = False

Dim s As Integer
s = 2

Do While s <> 19

Worksheets(s).Activate
Worksheets(s).Visible = True
Sheets("Stats").Select
s = s + 1

Loop

'creating temporary data page

Sheets("Stats").Select
Sheets.Add
ActiveSheet.Name = "Data"
Sheets("Data").Select
Sheets("Data").Move After:=Sheets(19)

'copying data

Dim j As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim x As String
Dim z As String

d = 1
j = 2
b = 2
c = 11

Do While j <> 19

Worksheets(j).Select
x = Range("A1")


Sheets("Data").Select
Range("A" & d).Select
ActiveCell.FormulaR1C1 = x

Worksheets(j).Select
Range("A27:B36").Select
Selection.Copy

Sheets("Data").Select
Range("A" & b).Select
ActiveSheet.Paste

Worksheets(j).Select
Range("A39").Select
Selection.Copy

Sheets("Data").Select
Range("A" & c).Select
ActiveSheet.Paste

j = j + 1
b = b + 19
c = c + 19
d = d + 19

Loop



'delete data
Do While j <> 19
j = 3
Worksheets(j).Activate
Range("A27:B36").Delete
Range("A39:I48").Delete
j = j + 1
Range("A1").Select
Loop


End Sub

Thanks for any help,
Brandon

 

[NdNoviceHlp]

There are 2 sections on the individual pages that have to be entered manually on each page and therefore do not move with the rest of the info. I have another macro that takes all of that info on each page and copies all of it to another individual page

Why not change the way the data is entered so that it is all entered on the template to begin with? Then you could just sort by sheet name. I also don't understand why some page data "do not move with the rest of the info"? I think someone will probably be able to provide you with a more efficient approach than the code posted if you provide some more info. Good luck. Dave

 

[Quagmire]

The way the template is setup is that the team lead copies and pastes the biweekly/monthly stats and quality scores from another cube and they go to each of the individual 16 pages. The 2 manual parts are directly for the individual csr including attendance violations and feedback. I have the mainpage so it prints 4 separate pages so they can provide this to management; therefore, I am unable to add this info to the mainpage. If this is added then the mainpage will print out unnecessary info for management and the mainpage will printout apprx 9-10 pages. Here is the code I have for the mainpage to sort by name and move the stats around:

Private Sub AddButton_Click()
Me.txtName.SetFocus
Application.ScreenUpdating = False
Dim i As Integer
Dim x As Boolean
Dim n As String
x = False
i = 66
n = Me.txtName.Value
Sheets("Stats").Select
ActiveSheet.Unprotect
Range("B62:Q62").Select
Selection.Copy
Range("B77").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B95").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Do While i <> 82 And x <> True

'w = Range(Chr(i) & 62)
If Range(Chr(i) & 62) = "" Then
Range(Chr(i) & 77).Value = n
Range("B63", Chr(i) & 77).Select
Selection.Sort Key1:=Range("B77"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Range(Chr(i) & 95).Value = n
Range("B81", Chr(i) & 95).Select
Selection.Sort Key1:=Range("B95"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Range("B77:Q77").Select
Selection.ClearContents
Range("B95:Q95").Select
Selection.ClearContents
x = True
Else
i = i + 1
End If
Loop
i = 3
x = False
Do While i <> 19

If (Range("A" & i) = "" And x <> True) Then
Range("A" & i).Select
ActiveCell = n
Range("A2:A18").Activate
Selection.Copy
Range("L22").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("L42").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("A3:L18").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C23:L38").Select
Selection.Sort Key1:=Range("L23"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C43:L58").Select
'edit test
Selection.Sort Key1:=Range("L43"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("L22:L38").Select
Selection.ClearContents
Range("L42:L58").Select
Selection.ClearContents
x = True
Else
i = i + 1
End If
Loop
Me.Hide
Me.txtName.Value = ""
Application.ScreenUpdating = True
Sheets("Stats").Select
ActiveSheet.Protect
Call SheetRename
Call Links
End Sub

Thanks,
B

 

[NdNoviceHlp]

Okay so you want to sort by name which represents a sheet? When you sort these names/sheets the manually entered parts don't sort along with the sheets? That's where I'm lost. I still think your template should should provide for this input...perhaps at the bottom as all of the sheet doesn't need to be printed. Anyways, I'm pretty sure this is doable. For example, you could just load the entire contents of each sheet into an array, sort the array and then re-enter the data in the sheets. Maybe a bit more info is needed. Dave