Code to copy, paste and summarize a data set

The code currently works as it should and seems to work dynamically as I need it to but I am pretty new to Excel so I think there is probably a better way to do some of the things I have set up. I also would like to make the code a bit prettier.

```
'***
'**This finds, copies, and moves the data to the correct sheets then formats them correctly***
'***

Sub Copy_Function_Data()
Dim Target As String
Dim X As Long
Dim Y As Long
Dim Target_2 As String
Dim Last_Row3 As Long
Dim Last_Column3 As Long
Dim Total_Count As Long
Dim Title_Column As Long
Dim Country_Column As String
Dim Row_Limit1, Row_Limit2 As Long
Dim Column_Limit1 As Long
Dim Row_Limit3 As Long
Dim Current_Sheet As String
Dim r As Range
Dim r2 As Range

Application.StatusBar = "Moving Data..."

Y = ActiveWorkbook.Worksheets("Calculations").Range("B3", Worksheets("Calculations").Range("B3").End(xlDown)).Rows.Count

For X = 1 To Y

Sheets("Calculations").Select
Target = Range("B2").Offset(X, 0)
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "TempData"
Worksheets("TempData").Range("A1").Value = Target
ActiveSheet.Range("A1").Select
Selection.Replace What:="Open Position - ", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Target_2 = Worksheets("TempData").Range("A1")
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = (Target_2)
Sheets("Raw Data").Select
Selection.AutoFilter
ActiveSheet.Range("$B$1:$Z$10000").AutoFilter Field:=5, Criteria1:= _
(Target)
Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(Target_2).Select
Range("B50").Select
ActiveSheet.Paste

The first thing I would address is using .Select or .Activate - it just slows the code down by needing to fiddle with the spreadsheet while doing everything else behind the scenes. There's a good question on StackOverflow addressing this - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros.

First thing to do would be to give some variables to your worksheets

Dim calcSheet as worksheet
set calcSheet = Thisworkbook.sheets("Calculations")
Dim rawSheet as worksheet
set rawSheet = thisworkbook.sheets("raw data")
ThisWorkbook.Sheets.Add.Name = "tempdata"
dim tempSheet as worksheet
set tempsheet = thisworkbook.sheets("tempdata")

You get the idea. Now everytime you need to work with a selection, just specify the sheet object instead. Also, worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet.

So for example, this -

For X = 1 To Y
Sheets("Calculations").Select
Target = Range("B2").Offset(X, 0)

Becomes this

For x = 1 to Y
    Target = calcSheet.cells(2+x,2)

Easy peasy!

So this

Range("B1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets(Target_2).Select
Range("B50").Select
ActiveSheet.Paste

Becomes this simple

Dim lastColumn As Long
Dim lastRow As Long
lastColumn = rawSheet.Cells(2, Columns.Count).End(xlToLeft).Column
lastRow = rawSheet.Cells(Rows.Count, 2).End(xlUp).Row
secondTarget.Range(Cells(50, 2), Cells(50 + lastRow, 2 + lastColumn)) = rawSheet.Range(Cells(1, 2), Cells(1 + lastRow, 2 + lastColumn)).Value

You'll notice I changed the method of finding the bounds. There is a standard way to find lastRow and lastColumn. That post explains why.

In this loop, you're creating a sheet everytime

For X = 1 To Y

Sheets("Calculations").Select
Target = Range("B2").Offset(X, 0)
ActiveWorkbook.Worksheets.Add

Make the sheet before the loop and if you need to, just clear it before the next iteration.

StackExchange Code Review Q#128805, answer score: 3