Solved

Copy worksheets from 1 workbook to another

Posted on 2014-03-15
4
362 Views
Last Modified: 2014-03-15
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)
0
Comment
Question by:upobDaPlaya
  • 2
  • 2
4 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39931703
#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
0
 

Author Comment

by:upobDaPlaya
ID: 39931733
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
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 39931739
That won't work.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Orders")
ws.Range("A1").Value = 123

Dim ws1 As Worksheets
Set ws = ThisWorkbook.Worksheets
ws("Orders").Range("A1").Value = 123

ws is a single worksheet. ws1 is a collection of worksheets.

Kevin
0
 

Author Closing Comment

by:upobDaPlaya
ID: 39932136
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...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question