I am trying to select cells in one workbook with multiple sheets and enter
them into multiple workbooks which would each be named and saved with the
same name as the worksheet they came from in the old workbook.

For example, the first worksheet in workbook "Trollie" is named "White". I
am opening another workbook (previously created, named "UDF") and copy cells
c79,c80,c81,c88,c89,c91,and c95 of "Trollie" into cells in column A of "UDF".
I then want to save "UDF" with the name "White" (as in the name of the
worksheet in "Trollie"). I need to do this with each worksheet in the
workbook "Tollie". Can anyone please help with this?

--
Thank you, Jodie

Thank you Joel. I tried it and it saved copies of the UDF with file names
equal to the sheet names in the Trollie workbook. However, the UDF files do
not have the cell information from the Trollie workbook. How can I get the
specified cells from the Trollie workbook to each of the new UDF files?

One more thing... I am also trying to populate cell A1 in the new UDF files
with the sheet names of the Trollie workbook. Would you know how to make
that happen?
--
Thank you so much, Jodie


"joel" wrote:

>
> Assume the macro will go into the workbook Trollie and the the UDF file
> and ne workbooks will be located in the same folder as Trollie.
>
> Sub CopySheets()
>
> 'asume the workbooks will be ased in the same
> 'folder as the current workbook
> Folder = ThisWorkbook.FullName
> 'remove filename from folder
> Folder = Left(Folder, InStrRev(Folder, "\"))
>
> For Each sht In Sheets
> FName = sht.Name
> Set bk = Workbooks.Open(Filename:=Folder & "UDF.xls")
> With bk.Sheets(1)
> sht.Range("A1") = .Range("C79")
> sht.Range("A2") = .Range("C80")
> sht.Range("A3") = .Range("C81")
> sht.Range("A4") = .Range("C88")
> sht.Range("A5") = .Range("C89")
> sht.Range("A6") = .Range("C91")
> sht.Range("A7") = .Range("C95")
> End With
>
> bk.SaveAs Filename:=Folder & FName
> bk.Close
> Next sht
>
> End Sub
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195354
>
> http://www.thecodecage.com/forumz
>
> .
>

You forgot the "dots" in front of the Range calls (for Column C) in your
"To" section (so they reference back to your With statemen)....

..Range("C79") = sht.Range("A1")
..Range("C80") = sht.Range("A2")
..Range("C81") = sht.Range("A3")
..Range("C88") = sht.Range("A4")
..Range("C89") = sht.Range("A5")
..Range("C91") = sht.Range("A6")
..Range("C95") = sht.Range("A7")

--
Rick (MVP - Excel)



"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> I moved the data in the wrong direction
>
> from
> sht.Range("A1") = .Range("C79")
> sht.Range("A2") = .Range("C80")
> sht.Range("A3") = .Range("C81")
> sht.Range("A4") = .Range("C88")
> sht.Range("A5") = .Range("C89")
> sht.Range("A6") = .Range("C91")
> sht.Range("A7") = .Range("C95")
>
> to
> Range("C79") = sht.Range("A1")
> Range("C80") = sht.Range("A2")
> Range("C81") = sht.Range("A3")
> Range("C88") = sht.Range("A4")
> Range("C89") = sht.Range("A5")
> Range("C91") = sht.Range("A6")
> Range("C95") = sht.Range("A7")
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread:
> http://www.thecodecage.com/forumz/sh...d.php?t=195354
>
> http://www.thecodecage.com/forumz
>

Joel, I switched them as you indicated and it is still not populating the
cells in the UDF files with the information from the Trollie workbook. I
don't get an error, it just doesn't do anything.

Rick, I tried with the second leading dot and I get an error.
--
Thank you, Jodie


"joel" wrote:

>
> I moved the data in the wrong direction
>
> from
> sht.Range("A1") = .Range("C79")
> sht.Range("A2") = .Range("C80")
> sht.Range("A3") = .Range("C81")
> sht.Range("A4") = .Range("C88")
> sht.Range("A5") = .Range("C89")
> sht.Range("A6") = .Range("C91")
> sht.Range("A7") = .Range("C95")
>
> to
> .Range("C79") = sht.Range("A1")
> .Range("C80") = sht.Range("A2")
> .Range("C81") = sht.Range("A3")
> .Range("C88") = sht.Range("A4")
> .Range("C89") = sht.Range("A5")
> .Range("C91") = sht.Range("A6")
> .Range("C95") = sht.Range("A7")
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195354
>
> http://www.thecodecage.com/forumz
>
> .
>

> Rick, I tried with the second leading dot and I get an error.

**Second** leading dot??? In the "To" section that Joel posted, he omitted
the leading dot altogether... all I was doing was noting that. If you put
the leading dot in on your own, then you were not suppose to put another one
in as a result of my posting... as I said, I was just correcting an omission
in Joel's message that I thought might possibly have given you a problem.

--
Rick (MVP - Excel)



"Jodie" <(E-Mail Removed)> wrote in message
news:6035EE58-AAA3-42FB-BF4D-(E-Mail Removed)...
> Joel, I switched them as you indicated and it is still not populating the
> cells in the UDF files with the information from the Trollie workbook. I
> don't get an error, it just doesn't do anything.
>
> Rick, I tried with the second leading dot and I get an error.
> --
> Thank you, Jodie
>
>
> "joel" wrote:
>
>>
>> I moved the data in the wrong direction
>>
>> from
>> sht.Range("A1") = .Range("C79")
>> sht.Range("A2") = .Range("C80")
>> sht.Range("A3") = .Range("C81")
>> sht.Range("A4") = .Range("C88")
>> sht.Range("A5") = .Range("C89")
>> sht.Range("A6") = .Range("C91")
>> sht.Range("A7") = .Range("C95")
>>
>> to
>> .Range("C79") = sht.Range("A1")
>> .Range("C80") = sht.Range("A2")
>> .Range("C81") = sht.Range("A3")
>> .Range("C88") = sht.Range("A4")
>> .Range("C89") = sht.Range("A5")
>> .Range("C91") = sht.Range("A6")
>> .Range("C95") = sht.Range("A7")
>>
>>
>> --
>> joel
>> ------------------------------------------------------------------------
>> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
>> View this thread:
>> http://www.thecodecage.com/forumz/sh...d.php?t=195354
>>
>> http://www.thecodecage.com/forumz
>>
>> .
>>

Sorry for the confunsion guys. I did have just one leading zero and it was
not bringing over the cell information. Would either of you know why?
--
Thank you, Jodie


"joel" wrote:

>
> Rick had two dots in front of RANGE. There should only be one dot.
>
>
>

> VBA Code:
> --------------------
>
>

> .Range("C79") = sht.Range("A1")
> .Range("C80") = sht.Range("A2")
> .Range("C81") = sht.Range("A3")
> .Range("C88") = sht.Range("A4")
> .Range("C89") = sht.Range("A5")
> .Range("C91") = sht.Range("A6")
> .Range("C95") = sht.Range("A7")

> --------------------
>
>
>
>
> The code I posted had the single dot but the dot for some reason didn't
> display in the posting.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195354
>
> http://www.thecodecage.com/forumz
>
> .
>

Sorry again... I meant to say Sorry for the confunsion guys. I did have just
one leading dot in front of Range and it was not bringing over the cell
information. Would either of you know why?
--
Thank you, Jodie


"Jodie" wrote:

> Sorry for the confunsion guys. I did have just one leading zero and it was
> not bringing over the cell information. Would either of you know why?
> --
> Thank you, Jodie
>
>
> "joel" wrote:
>
> >
> > Rick had two dots in front of RANGE. There should only be one dot.
> >
> >
> >
>
> > VBA Code:
> > --------------------
> >
> >
>
> > .Range("C79") = sht.Range("A1")
> > .Range("C80") = sht.Range("A2")
> > .Range("C81") = sht.Range("A3")
> > .Range("C88") = sht.Range("A4")
> > .Range("C89") = sht.Range("A5")
> > .Range("C91") = sht.Range("A6")
> > .Range("C95") = sht.Range("A7")
>
> > --------------------
> >
> >
> >
> >
> > The code I posted had the single dot but the dot for some reason didn't
> > display in the posting.
> >
> >
> > --
> > joel
> > ------------------------------------------------------------------------
> > joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> > View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195354
> >
> > http://www.thecodecage.com/forumz
> >
> > .
> >

That works! Thank you Joel, for all of your help. I also figured out how to
get the sheet name in cell A1.
--
Thanks again, Jodie


"joel" wrote:

>
> Oops. The workbooks were backwards
>
>
> .Range("A1") = sht.Range("C79")
> .Range("A2") = sht.Range("C80")
> .Range("A3") = sht.Range("C81")
> .Range("A4") = sht.Range("C88")
> .Range("A5") = sht.Range("C89")
> .Range("A6") = sht.Range("C91")
> .Range("A7") = sht.Range("C95")
>
> The dot in front of range is the UDF workbook and sht is the workbook
> where the macro is located.
>
>
> --
> joel
> ------------------------------------------------------------------------
> joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
> View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=195354
>
> http://www.thecodecage.com/forumz
>
> .
>