asked on

Copy worksheets from 1 workbook to another

I have a routine that imports worksheets from 1 workbbok into what I will call a master workbook. Part of the code is sheet.copy after:=ThisWorkbook.Sheets(11). Note I will always have 11 static worksheets in the Master workbook. Everyday I will load in 2 worksheets from another workbook into the Master workbook at tab 12 and tab 13 in the Master workbook.

#1
It works fine, but my confusion is if I look at VBA project explorer the number for the worksheet does not correspond to the number of the worksheet when I loop thru worksheets. I assume I can ignore this anamoly as my prcoess works, but I am curious on the difference

#2
Whats the difference between Dim ws As Worksheet and Dim ws As Worksheets

#3
Is there any way when I do sheet.copy after:=ThisWorkbook.Sheets(11) that it will automatically place the worksheet after worksheet 11 without me having to run a separate routine to delete any worksheets found after tab 11 prior to running the line of code :sheet.copy after:=ThisWorkbook.Sheets(11)

zorvek (Kevin Jones)

#1: That's because the tab name and code names (what are displayed in the project explorer) are not the same as the worksheet index (it's position in the collection of worksheets.)

#2: Worksheet is a single worksheet object. Worksheets is a collection of worksheet objects.

#3: Doing a copy after a specific position in the collection does what you want: an insert at that position. You do not need to delete any sheets after that position.

Kevin

upobDaPlaya

ASKER

So as an example when would I want to
dim ws as worksheet vs
dim ws1 as worksheets

if I was only dealing with 1 worksheet such as orders then
thisworkbook.ws.orders(....)

if multiple worksheets then ...
For each ws1 in thisworkbook.sheets

Just want to make sure I understand

ASKER CERTIFIED SOLUTION

zorvek (Kevin Jones)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

upobDaPlaya

ASKER

Excellent..Excellent..Excellent. That makes sense. I have always just gone along and accepted it as is..its great to understand it...it helps with the other pieces in my quest to become an expert..thx again...