Application screenupdating false application cutcopymode false

It is a very safe way to refer to worksheets as the sheet name could possibly be changed in future for any reason and if this does happen, the code is dead. Hi, Sheet8 is the worksheet that Combo Box7 resides in, along with the other items decribed above, charts etc.

application screenupdating false application cutcopymode false-45

$A$9 or if another workbook is involved, something like ='[bookone.xls]sheet 1'! To use in a macro (note use of a single quote within double quotes in first example) For csht = 1 To Active Workbook. Count 'worksheet or sheets Cells(c Row - 1 csht, c Col) = "'" & Sheets(csht). Value Next csht Sub ARRAY_sheetnames() Dim wksht As Worksheet Dim i As Long Dim wkshtnames() 'This is an array definition i = 0 For Each wksht In Active Workbook. Value End If Next cell End If 'reduce to names to be used Re Dim Preserve arr Names(1 To I) Sheets(arr Names).

$A2 Another way of creating your worksheet formula is to start by placing an equal sign on the formula bar to start a formula, then select a cell on the other sheet which could be in another open workbook. Name Cells(c Row - 1 csht, c Col 1) = Sheets(Sheets(csht). Worksheets i = i 1 Re Dim Preserve wkshtnames(1 To i) wkshtnames(i) = wksht. Select End Sub File Access with Visual Basic® for Applications, describes how to process files with the file input/output (I/O) functions that Visual Basic for Applications provides -- information on using Sequentiai, Random access, and Binary.

Cells(j, 35) = "" Then Go To 100 myconcat = myconcat & ws. Cells(1, 35) = myconcat myconcat = "" For j = 6 To 1000 If ws.

It's just slow and I don't know how to speed it up.

I tried to modify your code to remove any Select/Activate command in my opinion you don't need to unprotect the Sheet8 for the Text Box to update.

When the selection in Combo Box7 changes, Macro41 changes cells values in Sheet17, a Text Box (Active X Control) located below Combo Box7 in Sheet8 reads from the new cell value in Sheet17. After further experimentations, I discovered that you don't even need the sheet to be visible to operate on it. Screen Updating = True err Handle: End Sub Sub Macro41() Dim ws As Worksheet Set ws = Sheet17 Dim lrow As Long lrow = ws.

Using the sheet code will ensure that the code will always work. Sheet17 is the sheet that Macro41 works in, the code is below...

Select For j = 6 To 1000 If Cells(j, 35) = "" Then Go To 100 myconcat = myconcat & Cells(j, 35) Next j 100 Cells(1, 35) = myconcat myconcat = "" For j = 6 To 1000 If Cells(j, 38) = "" Then Go To 1000 myconcat = myconcat & Cells(j, 38) Next j 1000 Cells(1, 38) = myconcat End [email protected] Louis H, The OP is referring to the sheet code not the sheet name. Row Dim Copy Destination As Range Set Copy Destination = ws.

Screen Updating = True err Handle: End Sub You are right, there's always a better solution than using the Select command which can have side effects.

Paste Special Paste:=xl Paste Formulas, Operation:=xl None, _ Skip Blanks:=False, Transpose:=False Application.

For my personal knowledge, i'm not familiar with this reference to your sheets : @ Louis H, The OP is referring to the sheet code not the sheet name.

Tags: , ,