Help with LIKE in macro please

[Austinhead]

Hi Guys,

I have already had some great help on this forum, but once again I am stuck and am hoping someone can help me.

I have a spreadsheet with multiple worksheet names, I want to copy some data across from e.g. the last 5 worksheet to the first 5 worksheets. The complication is that the worksheet names for the first 5 can change (U2-A01 up to U2-A010, or U3-A01 up to U3-A010 e.t.c.) and each spreadsheet can have up to 20 worksheets.

Rather than having to change the macro each time, I was hoping to use the LIKE operator to find the appropriate worksheet to copy to. I cannot get this working, but that's not surprising since my macro knowledge is very very poor.

The code is below:

Sub Copy_Objectives()
Dim sh As Variant
'
'
' Copies objectives from Feedback sheets to Main Assessment objectives sheets
' Macro created 20/05/2010 by Susie ORiley
'
For Each sh In Array("A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A010")
If SheetExists(sh & " Feedback") Then
With Sheets(sh & " Feedback").Range("C4", Sheets(sh & " Feedback").Range("C4").End(xlDown))
End With
If Worksheet.Name Like ("U?-" & sh) Then
Range("D3").Resize(, .Count) = Application.Transpose(.Value)
Else

End If

End Sub

The array worksheet names can stay as is, but I need the macro to be able to find the "U?-" & sh so I don't have to edit this macro each time I (or someone else) creates a new spreadsheet.

Any help would be most appreciated.

Thanks in advance.

 

[xld]

What is that code supposed to do exactly?

 

[Austinhead]

The code selects cells from row C4 to the last populated cell on that row within worksheet A01, and copies to column D3 within worksheet U?-A01. It then repeats copying from worksheet A02 to worksheet U?-A02 e.t.c. until there are no more worksheets left.

 

[Austinhead]

Any ideas ?. Thanks in advance.

 

[Norie]

Why do you need to use like?

Is the ? a wildcard?

Is the range to copy always C4:CLastRow and the destination D3?

 

[Austinhead]

Hi Norie,

Yes the ? is a wildcard, since the ? represents a number which will be different for each spreadsheet I produce.

The range is always the same. I have got it working fine if I hardcode the U2-A01 .... worksheet name, but I'm just trying to make it more portable.

Thanks.

 

[Norie]

Could you not extract the no after the U at the start of the code and use that?

You might also want to consider not using a hard-coded array for A01, A02 etc

Is this code for a single workbook?

PS The code's a little confusing especially since you don't actually seem to be copying anything. In fact it doesn't even compile.

This will compile but it definitely won't do what you want.

Sub Copy_Objectives()
Dim sh As Variant
    '
    '
    ' Copies objectives from Feedback sheets to Main Assessment objectives sheets
    ' Macro created 20/05/2010 by Susie ORiley
    '
    For Each sh In Array("A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A010")

        If SheetExists(sh & " Feedback") Then

            With Sheets(sh & " Feedback").Range("C4", Sheets(sh & " Feedback").Range("C4").End(xlDown))
 
                If sh.Name Like ("U?-" & sh) Then

                    Range("D3").Resize(, .Count) = Application.Transpose(.Value)

                End If

            End With

        End If

    Next sh

End Sub

 

[Austinhead]

Thanks for your reply.

I am a complete novice at this, and don't have any coding background (as you can probably tell). I have been using bits and bobs from on here and other places to get stuff working.

The code is not for a single workbook (hence the problem), in that I will be creating new ones with different content, but the code does not need to copy from 1 workbook to another.

The code does not work that i posted previously, I was hoping someone could tell me why lol. I have copied the code that has been working, using a hardcoded name:

Sub Copy_Objectives()
'
'
' Copies objectives from Feedback sheets to Main Assessment objectives sheets
' Macro created 20/05/2010 by Susie ORiley
'
For Each sh In Array("A01", "A02", "A03", "A04", "A05", "A06", "A07", "A08", "A09", "A010")
If SheetExists(sh & " Feedback") Then
With Sheets(sh & " Feedback").Range("C4", Sheets(sh & " Feedback").Range("C4").End(xlDown))
Sheets("U2-" & sh).Range("D3").Resize(, .Count) = Application.Transpose(.Value)
End With
End If
Next
End Sub

If only it accepted wildcards this would be SO much easier !!.

Just to clarify. There will be up to 10 worksheets called A01 - Feedback, A02 - Feedback e.t.c. and the same number called U2-A01, U2-A02 e.t.c. The number after the U will be different for each workbook created.

I need to copy 1 column of data from each of the the Feedback sheets across to 1 row on each of the U*-A01. I then also have another macro to do the reverse.

I basically need my hand holding through this stuff lol

 

[Norie]

When I asked if it was only for one workbook I meant were all the sheets etc in the same workbook and no other workbooks would be involved.

If that was the case it would make things far easier as you wouldn't need to be referencing multiple workbooks.

I kind of realised that you'll probably be using it on different workbooks with the same structure, well I assume you are anyway.

Anyway back to the code, is this a decent summary of what you want to do?


1 Loop through worksheets.

2 If worksheet name is "A*- Feedback Then

3 Copy range C4:CLastRow

4 Transpose and paste to worksheet "U2-A*" at D3

5 End If

6 Goto next worksheet.

The main stumbling block I see here if the code is to be used for other workbooks is determining U2 or whatever.

I know it's part of some of the worksheets names but is it also available elsewhere. eg the workbook name

 

[Austinhead]

When I asked if it was only for one workbook I meant were all the sheets etc in the same workbook and no other workbooks would be involved.

Yes, all the sheets are in the same workbook.


1 Loop through worksheets.

Yes

2 If worksheet name is "A*- Feedback Then

Yes

3 Copy range C4:CLastRow

Yes

4 Transpose and paste to worksheet "U2-A*" at D3

Yes

5 End If

6 Goto next worksheet.

Yes

The main stumbling block I see here if the code is to be used for other workbooks is determining U2 or whatever.

I know it's part of some of the worksheets names but is it also available elsewhere. eg the workbook name

It is available elsewhere on the worksheet if that helps. The workbook name is more likely to say something like Unit2, and since I won't always be creating these (or I hope not) I don't think that can be relied on.

Thanks,
Susie.