[RESOLVED] Excel basic app too slow

Hi EXPERTS ,

I want to achieve something basic. Copy and pasting a sheet of data from one sheet into another sheet and renaming the new pasted info with the sheet name "starting point". The trouble is - it's very slow for something so straitforward. I know you experts - better coding. Can some help me code this better to make it faster.

VB Code:

Sub ProduceStartingPoint()
 
Dim arraydata() As String
Dim i As Long
Dim j As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim FileLength As Long
Dim RowCount As Long
Dim sheetcount As Integer
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Add worksheets after the last sheet
Worksheets.Add after:=Sheets(sheetcount)
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "Starting point"
 
'Activate worksheet
Worksheets(1).Activate
 
'Find FileLength
Range("A:A").Select
FileLength = Selection.Cells.Count
 
'Find RowCount
Range("1:1").Select
RowCount = Selection.Cells.Count
 
'Create ActiveCell
Range("A2").Activate
 
'set x to first column
x = 1
 
'set y to second row
y = 1
 
'Finds number of rows and columns
 
For j = 1 To RowCount
    If ActiveCell.Offset(0, 1) <> "" Then
    x = x + 1
    ActiveCell.Offset(0, 1).Activate
    Else
    GoTo nextrow
    End If
Next j
 
nextrow:
 
For i = 1 To FileLength
  y = y + 1
  Range("A" & y).Select
  If ActiveCell.Offset(1, 0) = "" Then
  GoTo InputArrayData
  End If
Next i
  
InputArrayData:
 
'ReDimension the array
ReDim arraydata(y, x) As String
 
For i = 1 To y
Worksheets(2).Activate
Range("A" & i).Select
Worksheets(1).Activate
Range("A" & i).Select
  For j = 1 To x
  Worksheets(1).Activate
  arraydata(y, x) = ActiveCell.Text
  ActiveCell.Offset(0, 1).Activate
  Worksheets(2).Activate
  ActiveCell.formula = arraydata(y, x)
  ActiveCell.Offset(0, 1).Activate
  Next j
Next i
 
End Sub

Cheers,

Borris.

Quote:

---

Originally Posted by gphillips

I want to achieve something basic. Copy and pasting a sheet of data from one sheet into another sheet and renaming the new pasted info with the sheet name "starting point". The trouble is - it's very slow for something so straitforward. I know you experts - better coding. Can some help me code this better to make it faster.

---

Utilize Excel's method to copy sheets:

VB Code:

Sheets(1).Copy After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = "Starting point"

:)

any code of that nature will run much faster if your display is hidden while the code is running

sheets(1).visible = false
hide all the sheets involved with the code

pete