Thank you keepITcool for code and your time.

Regards

Bob C.

"keepITcool" wrote:

Yes.. you can do everything without activating/selecting

you could NOT create a new sheet without activating it.
as it automatically becomes the activesheet when it is inserted.

Sub foo()
Dim i&, n&

Worksheets(1).Range("b6:d6").Copy
For i = 2 To Worksheets.Count
With Worksheets(i).Cells(Rows.Count, 2).End(xlUp).Offset(1)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False

Worksheets(1).Range("A1:H14").Copy
With Worksheets.Add(after:=Worksheets(Worksheets.Count) )
'ALTHOUGH it's now the activesheet
'I still use 'tunnelled' object referencing

For i = 1 To Worksheets.Count
If Worksheets(i).Name Like "NewSheet*" Then n = n + 1
Next
.Name = "NewSheet" & n + 1
.Paste .Range("B1")
End With
Application.CutCopyMode = False


End Sub

Sub foo2()
Dim i&, n&
'although normally you'd use object variables..
'you CAN do it with 'tunneling' and still precisely reference the
correct object.. even if 'all' is hidden

With Workbooks("Book1")
.Windows(1).Visible = False
.Worksheets(1).Range("b6:d6").Copy
For i = 2 To .Worksheets.Count
With .Worksheets(i)
.Visible = 0
With .Cells(.Rows.Count, 2).End(xlUp).Offset(1)
.PasteSpecial xlPasteFormats
.PasteSpecial xlPasteValues
End With
End With
Next
Application.CutCopyMode = False

.Worksheets(1).Range("A1:H14").Copy
With .Worksheets.Add(after:=.Worksheets(.Worksheets.Cou nt))
.Visible = 0
For i = 1 To .Parent.Worksheets.Count
If .Parent.Worksheets(i).Name Like "NewSheet*" Then n = n + 1
Next
.Name = "NewSheet" & n + 1
.Paste .Range("B1")
End With
Application.CutCopyMode = False

End With

End Sub




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


?B?Um9iZXJ0IENocmlzdGll?=
wrote :

KeepITcool

I've tried combinations of your code but can not achieve my aims;
which a- In Book1.xls and in sheet1, create a second sheet, then
without actually opening this second sheet2 rename it, put title
headers and format in row A.

Question: Can VBA code do that in the background without sheet2 being
activated (opening)?

If Yes, Then still in Sheet1 copy only the values and number formats
of the sheet1 Range("B6:D6"), into the first blank cell in Column B
Sheet2 (which would be cell B2).
A sub procedure is then called up which changes sheet1 Range("B6:D6")
values then copies the new values across to Sheet2 to the next blank
cell in Column B (B3).
The sub procedure includes a For Next Loop which at present has to
run/repeat 65 times.

Question: Can the opening of a new sheet, renaming, entering of title
headers, cell/column width formatting and entering an average formula,
all be carried out without selecting/activating sheet2?

What is the recognised way to go about this?
Do you activate Sheet1 and transfer data to sheet2 or activate sheet2
and reference back to sheet1?
If the later how would you call up the Sub Procedure and transfer the
data in the background?
Hope I have explained my aims with sufficient detail for you/everyone
to understand.

The Data values in Sheet1 are arrived at using Vlookup formulas from
other workbooks according to two dates on Sheet1.
The Sub Procedure changes these two dates thus altering the
Range(€œB6:D6€�) cell values.

Thank you for any help provided.

Regards Bob C.



"keepITcool" wrote:


i wouldnt use dt for a worksheet variable. naming conventions would
lead others to believe your workking with a date.

dim wsSrc as worksheet
dim wsDst as worksheet

'using this workbook as i assume the code resides
'in the same workbook as the sheet.
set wsSrc = Thisworkbook.worksheets("FOOD")
'alternative
'set wsSrc= Workbooks("Source.xls").worksheets("Food")

'now to assign the wsDst variable to the new sheet!
With Workbooks("Destination.xls")
set wsDst = .Worksheets.Add(after:=.worksheets(.worksheets.cou nt))
end with

wsDst.Range("a1").value = wsSrc.Range("a1").value
wsSrc.Range("b2:G20").copy wsDst.Range("b2:G20")

etc etc

it might be a good idea to buy a good excel book, as this is a
concept that you MUST learn/study in order to use VBA efficiently and
without frustration.

(WalkenBach's Excel Power Programming,
Bovey/Bullen's VBA Programmers Reference


Once you have mastered some principles(variables/string manipulation
and excel's cell referencing.. the rest is a lot easier :)
Dont forget the chapter on debugging and familiarize y'self with the
VBE. (breakpoints and the locals window!)


Then stick a printout of the Excel Object Model on the wall near your
monitor! (it's in VBA help.)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


?B?Um9iZXJ0IENocmlzdGll?=
wrote:

Hi everyone

Below is the start of a procedure to delete an existing worksheet
and create a new worksheet to replace old.
What changes to code is required to:-
If €ŀœFOOD€ÂÂ� sheet not included in workbook the Set dt
throws up and error, how do you skip the €ŀœFOOD€ÂÂ� delete
op. and Add a new €ŀœFOOD€ÂÂ� sheet anyway? How do you code
to open a new worksheet give it a tab name and 4 column title
header in A1 to D1 without actually selecting the new sheet?

Sub Test()
€˜
Dim dt As Worksheet
Set dt = Worksheets("FOOD")
€˜
ActiveWorkbook.Sheets("Where It Goes").Activate €˜
Application.DisplayAlerts = False
Worksheets("FOOD").Delete
Application.DisplayAlerts = True '
[C2].Value = [J1] €˜ enter start date in
€ŀœWhere It Goes€ÂÂ� worksheet. [G2].Value = [J2]
€˜ enter finish Date in €ŀœWhere It Goes€ÂÂ�
worksheet. ' ActiveWorkbook.Sheets.Add
After:=Worksheets(Worksheets.Count) ActiveSheet.Name = "FOOD"
ActiveSheet.Tab.ColorIndex = 4 '
I find it very difficult not to use select and activate each sheet
to achieve the copying and pasting of values formula etc. from one
sheet to next sheet or workbook.

All help greatly appreciated.