Before you start trying to do this, it looks as though you need to become a
bit more familiar with VBA and the Excel terminology.
> Dim Wkbk1 as Workbook (or worksheet)]
Which do you want, a workbook or a worksheet? Workbooks are files;
worksheets are the separate sheets inside a file.
> Wkbk1 = Sheets("OS TKT (TL)").Range("X4").Value
Sheets("OS TKT (TL)").Range("X4").Value is presumably returning the name of
a workbook file. This will be a String. But Wkbk1 is a Workbook object,
not a String. You need to open the workbook using its name and then Set the
workbook reference into Wkbk1.
> Dim Doc
> Dim Sheetname1
Please give these variables specific types ... probably String.
But what is a Doc anyway? Excel has workbooks and worksheets, not
doc(uments).
> Set Sheetname1 = ("M (10)") >>
Sheetname1 is a String, and should be declared that way. You don't use Set
on a simple assignment statement, so
Dim Sheetname1 as String
Sheetname1 = "M (10)"
> >>> I want to be able to assign and change sheet name "M (10)" here and
> use names from a list in another workbook/sheet.
That's the same sort of statement you were using to set Wkbk1 ... i.e.
indicate which cell you want to look at and get its value. If you don't
know how to do this, then you really need to learn some simple VBA before
you start trying to manipulate multiple workbooks.
> Sheetname1.Cells(iRow, 4).Value = Data (it's OK!)
If you fix this, it will change the value of the cell in the sheet named by
Sheetname1. Is this what you want to do? If so, then you presumably will
have a reference to the workbook that has this sheet in it, and you can do
something like
Wkbk2.Sheets(Sheetname1).Cells(iRow,4).Value = something
> CurJobNo = Sheets Sheetname1 .Range("D41").Value (Nothing Ive tried
> concatenating values has worked)
Sheetname1 is the name of the sheet, not a reference to the sheet.
Something like
CurJobNo = Sheets(Sheetname1).Range("D41").Value
would return the value of cell D41 from the specified sheet in the active
workbook.
Summary:
If you actually have any code written, please post it directly out of the VB
editor, and provide enough code. (E.g. you use iRow, but give no idea how
it is declared or where it gets its values from).
Please name your variables to indicate what they are actually used for.
Wkbk1, Wkbk2 and Sheetname1 are hardly informative variable names.
Please do not try to run before you can walk. :-) Become familiar with the
various parts of VBA: normal assignment statements, using cells on other
sheets, ranges, values, etc, before you even think about trying to write
code that includes multiple workbooks.
Once you start thinking about multiple workbooks, start small. Have your
program open one extra workbook, with a fixed name, and manipulate cells in
multiple sheets in these two books. This will teach you when to create
references and use them, so that your actions don't apply to the active
workbook and/or worksheet by accident. Multi-workbook programming is not
simple.
Regards, Dave S
----- Original Message -----
From: "laertetrad" <laertetrad@gmail.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, October 19, 2012 10:29 PM
Subject: [ExcelVBA] Using cell values from one sheet as workbook and
spreadsheet names
>
> Hi
>
> I have a list of workbooks and spreadsheet names in one spreadsheet to
> which I want to write values as appropriate.
>
> How can I use the cell value to define the workbook/spreadsheet which to
> open and write values to?
>
>
> I have tried something like:
>
> Dim Wkbk1 as Workbook (or worksheet)]
> Dim Wkbk2 as Workbook (or worksheet)]
> Dim Doc
> Dim Sheetname1
>
>
> Wkbk1 = Sheets("OS TKT (TL)").Range("X4").Value
> Doc = Sheets("OS TKT (TL)").Range("X12").Value
>
>
> Set Sheetname1 = ("M (10)") >>
>
> >>> I want to be able to assign and change sheet name "M (10)" here and
> use names from a list in another workbook/sheet.
>
> Sheetname1.Cells(iRow, 4).Value = Data (it's OK!)
>
> CurJobNo = Sheets Sheetname1 .Range("D41").Value (Nothing Ive tried
> concatenating values has worked)
>
>
> Summing up:
>
> Workbook1/Sheet N : contains list of workbooks and values to be written
>
> Workbook (?)/Sheet (?): workbook/sheets to be writen to, names (?) from
> lists in Workbook1
>
>
>
> Many thanks
>
>
> Laerte
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1427 / Virus Database: 2441/5340 - Release Date: 10/18/12
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar