| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding ws information to cell in new wb & saving with ws name
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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" wrote in message ... 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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 . |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding ws information to cell in new wb & saving with ws name
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving information to PERSONAL.XLS | Excel Programming | |||
| COPYING AND SAVING THE INFORMATION | Excel Discussion (Misc queries) | |||
| Saving information between uses? (Userform) | Excel Programming | |||
| adding cell data into a macro to allow specified information filte | Excel Programming | |||
| Saving Information on Userforms | Excel Programming | |||
Linear Mode
