INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Good afternoon, I have some simple code that would copy a range from some workbooks and paste as values in another workbook - that works fine.

CODE

Private Sub CopyDataButton_Click_Old()
Dim x As Integer
Dim wb1 As String 'First Workbook
Dim wb2 As String 'Second Workbook
Dim LastSheet As Integer

wb1 = "MARPROV.xls"
wb2 = "MARPROV_2.xlsx"

LastSheet = Workbooks(wb1).Sheets.Count - 5

For x = 3 To LastSheet

Application.ScreenUpdating = False

    Workbooks(wb1).Sheets(x).Range("J11:W11").Copy
    Workbooks(wb2).Sheets(x).Range("J11").PasteSpecial (xlPasteValues)

Next x

Application.ScreenUpdating = True

MsgBox ("All Data Copied")
End Sub 

The first workbook is linked by formulae to a known output file.
I've just been told that the second workbook would only contain worksheets that contain data as it is uploaded to a Government portal. I thought that I would put in a check to compare the names and only copy if they matched. Now, even before I've figured out how to execute a loop to find the corresponding worksheet in the second workbook it fails to execute - Sheets 4 are both aggregate sheets so they exist in both.

CODE

LastSheet = Workbooks(wb1).Sheets.Count
LastSheetTwo = Workbooks(wb2).Sheets.Count

For y = 4 To LastSheetTwo

For x = 4 To LastSheet

''Application.ScreenUpdating = False
MsgBox (Workbooks(wb1).Sheets(x).Name) 'This is temporary - just to check the names
MsgBox (Workbooks(wb2).Sheets(y).Name) 'This is temporary - just to check the names
    If Workbooks(wb1).Sheets(x).Name = Workbooks(wb2).Sheets(y).Name Then
    
        Workbooks(wb1).Sheets(x).Range("J11:W11").Copy
        Workbooks(wb2).Sheets(y).Range("J11").PasteSpecial (xlPasteValues)

    End If
    
Next x

Next y 

When I get to the (xlPasteValues) line it errors:-

Quote:


Run-time error '1004':
Application-defined or object-defined error

What has gone wrong and can you help with the syntax for a loop to look for the correct workbook - which will always be in wb1?

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Ah, the Aggregate sheet (4) is password protected so I can't paste into it.

OK, that just leaves me with this issue of how to write a loop to find the corresponding worksheets in wb2.

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Hi,

In general...

CODE

dim ws as Worksheet

For Each ws in SomeWorkbook.Worksheets
   'Now do stuff to ws
Next 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Hi Skip, yes I agree. I just don't want it to overwrite certain worksheets.

It's not a massive issue as the maximum number of worksheets in wb1 is 67 but I'd like to be able to exit the inner loop once the copy/paste has been executed. I would use a GoTo but I'm sure they're frowned upon! Is there another way?

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

" I just don't want it to overwrite certain worksheets"

CODE

dim ws as Worksheet

For Each ws in SomeWorkbook.Worksheets
   Select Case ws.Nme
      Case "This Sheet", "That Sheet", "The Other Sheet"
      Case Else
         'do something to these sheets
   End Select
Next 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
I'll remember that, thanks Skip.

What would you recommend as the neatest way of exiting the inner loop once the worksheets have matched?

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Well does it really matter? It will be a few micro seconds to complete the loop.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

"the neatest way of exiting the [...] loop"

CODE

dim ws as Worksheet

For Each ws in SomeWorkbook.Worksheets
   Select Case ws.Nme
      Case "This Sheet", "That Sheet", "The Other Sheet"
      Case Else
         'do something to these sheets
   End Select
   If IamDoneLooping Then
      Exit For
   End If
Next 

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Well HOW do you know that you're done? Is the case wher there's only ONE sheet to process, I'd agree: exit when that sheet is done.

The OP only stated that certain sheets were not to be processed. That means the sheets to be processed are undetermined.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

That was just my suggestion of "the neatest way of exiting the loop" and OP would have to specify when the loop is done.
I agree with you Skip that this is not really needed.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

So the key question is, when is the loop done? We have no logic for this.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Good morning guys. It's Friday and my boss isn't here! Happy days. Anyway, the first 4 worksheets in the Government-supplied 'template' workbook (wb2) are for notes, a control sheet, a hidden sheet and an aggregate sheet - I'll always need to ignore them. Our user will go to the control sheet and add worksheets according to which areas patients belong to - this can vary from month to month and we're only allowed to upload sheets that contain data, so this is what I'd use as my starting point. The 'raw data' template (wb1) has ALL patient area sheets as it looks up to a spreadsheet produced from our patient data on a monthly basis.

So wb1 will contain ALL patient areas but wb2 will only contain a sub-set of these areas. I figured that I would work my way through the worksheets in wb2 - starting at number 5 - and then loop through the worksheets in wb1 until I found a match:

CODE

If Workbooks(wb1).Sheets(x).Name = Workbooks(wb2).Sheets(y).Name Then
    
        Workbooks(wb1).Sheets(x).Range("J11:W11").Copy
        Workbooks(wb2).Sheets(y).Range("J11").PasteSpecial (xlPasteValues)

    End If 

So this will determine when then inner loop - Workbooks(wb1).Sheets(x) - is done and then it can move on to the next sheet in wb2.

As Skip says, this only takes micro seconds but I may need to apply this principle to other workbook in the future.

This works to quit the inner loop:

CODE

If Workbooks(wb1).Sheets(x).Name = Workbooks(wb2).Sheets(y).Name Then
    
        Workbooks(wb1).Sheets(x).Range("J11:W11").Copy
        Workbooks(wb2).Sheets(y).Range("J11").PasteSpecial (xlPasteValues)
GoTo line10
    End If
    
Next x
line10:
Next y 

I have decided to give the "Case" a go to loop through wb2 - even though I think that this will require a nested Case to loop through the sheets in wb1 to find a match -

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
But I'm having a syntax problem:

CODE

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet

wb1 = "MARPROV.xls"
wb2 = "MARPROV Template.xls"

For Each ws2 In wb2.Worksheets
   Select Case ws2.Name
      Case Len(ws2.Name) > 3
      Case Is <> "NONC"
      Case Else
      
        For Each ws In wb1.Worksheets
            Select Case ws.Name
            Case Is = ws2.Name
                wb1.ws.Range("J11:W11").Copy
                wb2.ws.Range("J11").PasteSpecial (xlPasteValues)
                Exit For
            End Select
        Next
      
   End Select

Next 

It immediately errors on wb1 =

Run-time error 91:
Object variable or With variable not set
I tried to edit the previous post but couldn't see a 'Post' button

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Wb1 & wb2 are declared as Workbook objects. Yet you are assigning a STRING!

CODE

Set wb1 = Workbooks("Some Name") 
...for instance.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Hi Skip, I had tried various incorrect combinations in the "Set". Thanks.

What I want to do in the first 'Case' is to ignore any worksheet name that's longer than 3 characters - as that's the area code length - apart from "NONC" as that's given to any that don't have a valid code in their records, for which I've put:

CODE

Case Len(ws2.Name) = 3, Is = "NONC" 

So I was hoping that this would mean that as "07K" is 3 characters long that it would start to process the inner part but it just jumps to 'Case Else'. (I just put the Msgbox in to check what sheet it was looking at - some are hidden - and I thought it was doing OK as it ignored the first few with long names.)

CODE

For Each ws2 In wb2.Worksheets
   MsgBox (ws2.Name)
   Select Case ws2.Name
   
      Case Len(ws2.Name) = 3, Is = "NONC"

        For Each ws In wb1.Worksheets
            Select Case ws.Name
            Case Is = ws2.Name
                ws.Range("J11:W11").Copy
                ws2.Range("J11").PasteSpecial (xlPasteValues)
                Exit For
            End Select
        Next

      Case Else
      
   End Select 


Any idea why its not doing what I'd hoped it would?

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Well, I've managed to do a workaround after I found that 'Case Is = "NONC" was working':

CODE

Select Case Len(ws2.Name)

      Case Is <= 4 

There is another worksheet in wb2 called "LAST" but as there isn't a corresponding one in wb1 it doesn't find a match in the inner 'Case' and does nothing.

This is the full code:

CODE

rivate Sub CopyDataButton_Click_Using_Case()
Set wb1 = Workbooks("MARPROV.xls")
Set wb2 = Workbooks("MARPROV Template.xls")
Dim ws As Worksheet
Dim ws2 As Worksheet

Application.ScreenUpdating = False

For Each ws2 In wb2.Worksheets

   Select Case Len(ws2.Name)

      Case Is <= 4
      
        For Each ws In wb1.Worksheets
        
            Select Case ws.Name
            Case Is = ws2.Name
                ws.Range("J11:W11").Copy
                ws2.Range("J11").PasteSpecial (xlPasteValues)
                Exit For
            End Select
            
        Next

      Case Else
      
   End Select

Next

Application.ScreenUpdating = True

MsgBox ("All Data Copied")

End Sub 

Many thanks,
D€$

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

Wouldn't be easier to do just:

CODE

For Each ws2 In wb2.Worksheets
   If Len(ws2.Name) <= 4 Then
        For Each ws In wb1.Worksheets
            Select Case ws.Name
                Case Is = ws2.Name
                    ws.Range("J11:W11").Copy
                    ws2.Range("J11").PasteSpecial (xlPasteValues)
                    Exit For
            End Select
        Next
   End 
Next 
???

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Excel 2010 Check if Worksheet Exists Then Copy Range and Paste As Values

(OP)
Ah, which then enables me to only act only on those with a worksheet length of 3 or = "NONC"

CODE

For Each ws2 In wb2.Worksheets
   If Len(ws2.Name) = 3 Or ws2.Name = "NONC" Then
        For Each ws In wb1.Worksheets
            Select Case ws.Name
                Case Is = ws2.Name
                    ws.Range("J11:W11").Copy
                    ws2.Range("J11").PasteSpecial (xlPasteValues)
                    Exit For
            End Select
        Next
   End If
Next 

Cheers Andy.

Many thanks,
D€$

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close