Return to Answer

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 - http://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.

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.

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 - http://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))

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.

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 - http://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.

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 - http://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!

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.

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 - http://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))

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.