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.