?
Solved

Copy worksheets from 1 workbook to another

Posted on 2014-03-15
4
Medium Priority
?
373 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

741 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