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

[PWD]

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.

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.

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:-

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€$

 

[PWD]

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€$

 

[SkipVought]

Hi,

In general...

dim ws as Worksheet

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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€$

 

[SkipVought]

I just don't want it to overwrite [highlight #FCE94F]certain worksheets[/highlight]"

dim ws as Worksheet

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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€$

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

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

dim ws as Worksheet

For Each ws in SomeWorkbook.Worksheets
   Select Case ws.Nme
      Case "This Sheet", "That Sheet", "The Other Sheet"
      Case Else[green]
         'do something to these sheets[/green]
   End Select[blue]
   If IamDoneLooping Then
      Exit For
   End If[/blue]
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.

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

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.

 

[SkipVought]

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

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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:

    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:

    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€$

 

[PWD]

But I'm having a syntax problem:

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€$

 

[SkipVought]

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

Set wb1 = Workbooks("Some Name")

...for instance.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[PWD]

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:

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.)

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€$

 

[PWD]

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

   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:

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€$

 

[Andrzejek]

Wouldn't be easier to do just:

For Each ws2 In wb2.Worksheets[blue]
   If Len(ws2.Name) <= 4 Then[/blue]
        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[blue]
   End [/blue]
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.

 

[PWD]

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

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€$