MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Workbook and Worksheet manipulation

  Asked By: Trupti    Date: Mar 22    Category: MS Office    Views: 1423

I am playing with vba at the moment and required a bit of a explanation
about techniques

What i was trying to achieve was from workbook, run code to open up another
workbook on the network

Select the workbook

Check if a worksheet already exists - if not create a template sheet from
the same book and name it appropriately

Then close it returning some item to the current workbook

So my code so far

Dim wbook as Object, wsheet As Object

Set wBook = Workbooks()

With wBook

.Open filename:=str_sbodirectory & str_sbofilename

Set wSheet = wBook.Sheets(str_sboworksheetdate).Select

'str_sboworksheet is a dynamic string which set the sheet name as the
current month and the year ie JUN 08

If wSheet Is Nothing Then

Worksheets("Master").Copy Before:=Master

End If

End With

Now first lack of understanding is the wbook being declared as a object, i
cant seem to manipulate the wbook its open.. is it because of declaring it
as an object instead of workbook

Then i cant seem to get any wsheet function to work and always coming with
subscript out of a range.

I wanted to try and refer by wookbook and worksheet as i will have 3
workbook open at the same time.. how should i approach this

I think i need more on the understanding of why.. rather than do this.. so
if you could point me in the right direction and where my logical approach
is going wrong.



2 Answers Found

Answer #1    Answered By: Alisha Johnson     Answered On: Mar 22

Dim wbook As Workbook, wsheet As Worksheet

Set wbook = Workbooks.Open(Filename:=str_sbodirectory & str_sbofilename)

With wbook

On Error Resume Next
Set wsheet = wbook.Sheets(str_sboworksheetdate)
On Error GoTo 0

If wsheet Is Nothing Then

.Worksheets("Master").Copy Before:=.Worksheets("Master")
End If

End With

Answer #2    Answered By: Varick Fischer     Answered On: Mar 22

Just do it with the macro recorder.... Stop it ...and take a look at the code

Didn't find what you were looking for? Find more on Workbook and Worksheet manipulation Or get search suggestion and latest updates.