I would like to copy and paste the values from a worksheet (HS-Detail) in a file to different tabs depending on the value in column A (Regions). I have a macro that works but it pastes the formulas and it takes quiet a while to run due to the number of records. How can I alter this to paste just the values and speed up the macro?
rcnt = ActiveWorkbook.Sheets("HS - Detail").Range("A" & Rows.Count).End(xlUp).Row
MTrec = ActiveWorkbook.Sheets("MOUNTAIN").Range("A" & Rows.Count).End(xlUp).Row
NCrec = ActiveWorkbook.Sheets("NORTH CENTRAL").Range("A" & Rows.Count).End(xlUp).Row
NErec = ActiveWorkbook.Sheets("NORTHEAST").Range("A" & Rows.Count).End(xlUp).Row
SCrec = ActiveWorkbook.Sheets("SOUTH CENTRAL").Range("A" & Rows.Count).End(xlUp).Row
SErec = ActiveWorkbook.Sheets("SOUTHEAST").Range("A" & Rows.Count).End(xlUp).Row
WErec = ActiveWorkbook.Sheets("WEST").Range("A" & Rows.Count).End(xlUp).Row
For i = 8 To rcnt
If ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "MOUNTAIN" Then
MTrec = MTrec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("MOUNTAIN").Range("A" & MTrec))
ElseIf ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "NORTH CENTRAL" Then
NCrec = NCrec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("NORTH CENTRAL").Range("A" & NCrec))
ElseIf ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "WEST" Then
WErec = WErec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("WEST").Range("A" & WErec))
ElseIf ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "NORTHEAST" Then
NErec = NErec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("NORTHEAST").Range("A" & NErec))
ElseIf ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "SOUTHEAST" Then
SErec = SErec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("SOUTHEAST").Range("A" & SErec))
ElseIf ActiveWorkbook.Sheets("HS - Detail").Range("A" & i).Value = "SOUTH CENTRAL" Then
SCrec = SCrec + 1
ActiveWorkbook.Sheets("HS - Detail").Rows(i & ":" & i).Copy (ActiveWorkbook.Sheets("SOUTH CENTRAL").Range("A" & SCrec))
End If
Next
Bookmarks