Adding ws information to cell in new wb & saving with ws name

[Jodie]

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?

 

[joel]

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

 

[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?

 

[joel]

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

 

[Rick Rothstein]

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

 

[Jodie]

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.

 

[Rick Rothstein]

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.

 

[joel]

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'
display in the posting.

 

[Jodie]

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

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

VBA Code:

 

[joel]

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.

 

[Jodie]

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?

 

[Jodie]

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