Prev: Array computation in vba
Next: How to Run a Macro From WB1 in WB2 Against WB2
From: Jodie on 13 Apr 2010 16:05
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
From: joel on 13 Apr 2010 17:03

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/showthread.php?t=195354

http://www.thecodecage.com/forumz

From: Jodie on 14 Apr 2010 09:50
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/showthread.php?t=195354
>
> http://www.thecodecage.com/forumz
>
> .
>
From: joel on 14 Apr 2010 10:04

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/showthread.php?t=195354

http://www.thecodecage.com/forumz

From: Rick Rothstein on 14 Apr 2010 10:25
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" <joel.49ew7n(a)thecodecage.com> wrote in message
news:joel.49ew7n(a)thecodecage.com...
>
> 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/showthread.php?t=195354
>
> http://www.thecodecage.com/forumz
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Array computation in vba
Next: How to Run a Macro From WB1 in WB2 Against WB2