Solved

Copy worksheets from 1 workbook to another

Posted on 2014-03-15
4
364 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

724 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