Need help with this macro 3

[markwilliams2004]

I am trying to write an excel macro that will copy a range called "Data" on sheet 1 into a series of sheets that are indicated by a range called "CountryNames" on sheet 2. So for each sheet I want to copy the information to there is an entry in the range containing that sheet name.

I cannot simply copy to all sheets in the workbook since not all sheets need the information copied over.

Any help is greatly appreciated. Relatively new to this VBA world.

I have copied some code to give an idea of what I am trying to do. I would like to have it loop through the names in a range to post to that named sheet.


range("A2:E7897").Select ---> This is a named range "Data"
application.CutCopyMode = False
selection.copy
Sheets("Other International").Select
range("A7").Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("International Eliminations").Select
range("A7").Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("European Support").Select
range("A7").Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[Bong]

It doesn't seem to me that your sample code is doing what you describe, although I found it somewhat difficult to follow your description. I gather that you want to:
[ul][li]copy the data in a specific range on sheet1[/li]
[li]look in another specific range on sheet2 for a list of tertiary sheet names[/li]
[li]past the data into each sheet named above[/li][/ul]
Is that it?

_________________
Bob Rashkin

 

[markwilliams2004]

Yes that is correct.

 

[Bong]

OK. For the sake of argument, let's say the range with the data is named "rData", and the range with the sheet names is named "rNames". You don't need to refer to the sheets for those ranges since the name definition includes the sheet reference.

Sub test()
    For Each n In Range("rNames").Cells.Value
        If Len(n) <> 0 Then Range("rData").Copy Destination:=Worksheets(n).[a1]
    Next
End Sub

You should probably do some error handling to account for the possibility that a sheet name in "rNames" might not exist.

_________________
Bob Rashkin

 

[SkipVought]

rather

For Each n In Range("rNames")[s].Cells.Value[/s]
...

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Bong]

Oh! That is more elegant, indeed.

_________________
Bob Rashkin

 

[markwilliams2004]

Ok sorry and thanks in advance for your patience.

I think I see where its going but when i try to run it I'm getting a run "type mismatch" error on this:

Range("rData").Copy Destination:=Worksheets.[a1]

Any thoughts?

 

[Bong]

Is your data range actually named "rData"?

_________________
Bob Rashkin

 

[SkipVought]

...or does Worksheets actually exist in this workbook? I'd suggest using the Watch WIndow.

faq707-4594

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[ck1999]

Mark,

I was looking at this code and copied it into vba and then mad e the named ranges as mentioned and I got the same error.

Then I copied and pasted the code again and it worked.

SO try and delete the code and retype or copy it

I duplicated this result to make sure i am not loco.

ck1999

 

[markwilliams2004]

You are all awesome! Thank you so much for the help.

If I want to paste as values the copied range, where do I incorporate that into this code?

 

[ck1999]

Try this

Sub test()
    For Each n In Range("rNames").Cells.Value
        If Len(n) <> 0 Then
            Range("rData").Copy
            Worksheets(n).[a1].PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
               :=False, Transpose:=False
        End If
          
    Next
    Application.CutCopyMode = False
End Sub

you could move Range("rData").Copy before the for loop for the same effect.

ck1999

 

[markwilliams2004]

That worked perfectly. I appreciate all the help and insight.

If anyone has a good reference manual that can help me build my knowledge, that feedback is welcome!