Greetings to all,
Not very experienced in vba at all, just enough to be dangerous.
Willing to learn, however. Here is my problem: I have a Worksheet wit
a multitude of sheets, each with a Unit name. There is a table in th
first sheet (UHU) with all the Unit names and some data next to eac
name. I would like to copy the relevant data for each unit from thi
sheet to the relevant sheet according to name. I am almost there,
can make it all the way to the actual sheet where I would like to past
my data, however, I get an error when I try to select a cell to paste m
data. I get error 1004 "Method 'Range' of object '_Global' failed" You
help would REALLY be appreciated! Thank you

Sheets("UHU").Activate
Range("B7").Activate
Range("C7:F7").Select
Selection.Copy
For Each c In Worksheets("UHU").Range("C7:C24")
Units_Sht_Name = c.Offset(0, -1).Value
With Worksheets
Worksheets(Units_Sht_Name).Activate
Range("09").Select
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial

End With

Next c

End Su

--
oakma
-----------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...nfo&userid=917
View this thread: http://www.excelforum.com/showthread.php?threadid=52539

Hi oakman

There is code on this page that you can try
http://www.rondebruin.nl/copy5.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"oakman" wrote in message
...

Greetings to all,
Not very experienced in vba at all, just enough to be dangerous.
Willing to learn, however. Here is my problem: I have a Worksheet with
a multitude of sheets, each with a Unit name. There is a table in the
first sheet (UHU) with all the Unit names and some data next to each
name. I would like to copy the relevant data for each unit from this
sheet to the relevant sheet according to name. I am almost there, I
can make it all the way to the actual sheet where I would like to paste
my data, however, I get an error when I try to select a cell to paste my
data. I get error 1004 "Method 'Range' of object '_Global' failed" Your
help would REALLY be appreciated! Thank you

Sheets("UHU").Activate
Range("B7").Activate
Range("C7:F7").Select
Selection.Copy
For Each c In Worksheets("UHU").Range("C7:C24")
Units_Sht_Name = c.Offset(0, -1).Value
With Worksheets
Worksheets(Units_Sht_Name).Activate
Range("09").Select
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial

End With

Next c

End Sub


--
oakman
------------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=525393

You are trying to select cell "09" (zero nine), which won't work. Did you
mean "O9" (with the letter O)?


Hutch

"oakman" wrote:


Greetings to all,
Not very experienced in vba at all, just enough to be dangerous.
Willing to learn, however. Here is my problem: I have a Worksheet with
a multitude of sheets, each with a Unit name. There is a table in the
first sheet (UHU) with all the Unit names and some data next to each
name. I would like to copy the relevant data for each unit from this
sheet to the relevant sheet according to name. I am almost there, I
can make it all the way to the actual sheet where I would like to paste
my data, however, I get an error when I try to select a cell to paste my
data. I get error 1004 "Method 'Range' of object '_Global' failed" Your
help would REALLY be appreciated! Thank you

Sheets("UHU").Activate
Range("B7").Activate
Range("C7:F7").Select
Selection.Copy
For Each c In Worksheets("UHU").Range("C7:C24")
Units_Sht_Name = c.Offset(0, -1).Value
With Worksheets
Worksheets(Units_Sht_Name).Activate
Range("09").Select
ActiveCell.Offset(0, 1).Activate
Selection.PasteSpecial

End With

Next c

End Sub


--
oakman
------------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=525393

You know, that is a great point.
I did not bother to check that what I chose was the letter "o" rather
than the number zero.
If I did choose the number, I cannot believe my absent mindedness.

Thank you so much to both replies.
They were extremely helpful!


--
oakman
------------------------------------------------------------------------
oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172
View this thread: http://www.excelforum.com/showthread...hreadid=525393