Open and format new worksheet without selecting it?

[Guest]

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.

--
Thank you

Regards

Bob C
Using Windows XP Home + Office 2003 Pro

 

[keepITcool]

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.count))
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 >

 

[Guest]

Hi keepITcool

Thanks for the help with my code, very much appreciated. I took one of your
colleagues advice from a while back and recently purchased “John WalkenBach's
Excel 2002 Power Programming with VBA†and have just started to read and try
to absorb it’s contents with my 61 year old brain, it’s shaping up as hard
read I feel.
But we’ll get there in end or hopefully before my end <bg>. I’ll take on
your advice about the print up as well.

Thank you once again and kept up the excellent help you and others in this
newsgroup provide.
It is appreciated by many many people I’m sure.

Regards

Bob C.

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.count))
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 >

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.

 

[Guest]

KeepITcool

I've tried combinations of your code but can not achieve my aims; which are:-
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("B66"), 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("B66") 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(“B66â€)
cell values.

Thank you for any help provided.

Regards Bob C.

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.count))
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 >

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.

 

[keepITcool]

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.Count))
.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 >

KeepITcool

I've tried combinations of your code but can not achieve my aims;
which are:- 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("B66"), 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("B66")
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(“B66â€) cell values.

Thank you for any help provided.

Regards Bob C.

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.count))
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 >


=?Utf-8?B?Um9iZXJ0IENocmlzdGll?=

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.

 

[Guest]

Thank you keepITcool for code and your time.

Regards

Bob C.

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.Count))
.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 >

KeepITcool

I've tried combinations of your code but can not achieve my aims;
which are:- 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("B66"), 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("B66")
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(“B66â€Â) cell values.

Thank you for any help provided.

Regards Bob C.

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.count))
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 >


=?Utf-8?B?Um9iZXJ0IENocmlzdGll?=

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.