[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

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)
0
upobDaPlaya
Asked:
upobDaPlaya
  • 2
  • 2
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
#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
 
upobDaPlayaAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
upobDaPlayaAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now