Prev: Autocomplete doesn't function properly
Next: Get Column Header Value
From: WLMPilot on 5 May 2010 08:24
I would like to know how to take data in cell A1 of Workbook1.Sheet1 and
place it in cell A1 of Workbook2.Sheet2 via a macro from workbook1?

Thanks.
From: Wouter HM on 5 May 2010 08:30
On 5 mei, 14:24, WLMPilot <WLMPi...(a)discussions.microsoft.com> wrote:
> I would like to know how to take data in cell A1 of Workbook1.Sheet1 and
> place it in cell A1 of Workbook2.Sheet2 via a macro from workbook1?
>
> Thanks.

Hi,

Your question is very short.
Based on this information I can only give this example:

Sub Copy1Cell()
Workbooks("Workbook2").Worksheets("Sheet2").Range("A1").Value = _
Workbooks("Workbook1").Worksheets("Sheet1").Range("A1").Value
End Sub

HTH,

Wouter
From: Bob Umlas, Excel MVP on 5 May 2010 08:38
Sub Answer()
Workbooks("Workbook2.xls").Sheets("Sheet2").Range("A1").Value =
Thisworkbook.Sheets("Sheet1").Range("A1").Value
End Sub

"WLMPilot" wrote:

> I would like to know how to take data in cell A1 of Workbook1.Sheet1 and
> place it in cell A1 of Workbook2.Sheet2 via a macro from workbook1?
>
> Thanks.
From: WLMPilot on 5 May 2010 10:28
Thanks. I have one more question pertaining to this. The master workbook
(workbook1) will be used by the boss while all other workbooks will have the
an employee's name as the filename.

I want the boss to be able to add to the master list any criteria
(questions) he would like to add to the list already in place. I will have
an adjacent cell to the criteria (for QA purposes) that will indicate the
date the criteria was added.

I am guessing I need to use the xlUP to locate the first empty row at the
bottom of the list in each employee workbook. So, given the following data,
what would the code be:

WORKBOOK1 (Master).Sheet1
NOTE: Boss adds a new criteria to QA list in B30 with date added in A30.
Need to
update all workbooks for each employee.
A30 = 05/05/10 (date format)
B30 = Are all signatures obtained? (text format)
D1:D? = Each employees name (Unknown how many rows needed for employees)

WORKBOOK2-?? (Each named for an employee listed in workbook1
SHEET1
A1-A? = list of criteria based on master list

Thanks,
Les

"Bob Umlas, Excel MVP" wrote:

> Sub Answer()
> Workbooks("Workbook2.xls").Sheets("Sheet2").Range("A1").Value =
> Thisworkbook.Sheets("Sheet1").Range("A1").Value
> End Sub
>
> "WLMPilot" wrote:
>
> > I would like to know how to take data in cell A1 of Workbook1.Sheet1 and
> > place it in cell A1 of Workbook2.Sheet2 via a macro from workbook1?
> >
> > Thanks.
From: Wouter HM on 6 May 2010 14:22
Hi there,

I have some remarks to start with

For starters may I suggest you put the names of the employees on
sheet2.
You should keep in mind that an employee may have his/her personal
workbook in use when the boss tries to update it.
So copy all questions every time the update proces is running.

It might be something like:

' --------- Start of code
Sub UpdateEmployeeSheets()

Dim strFolder As String

Dim strFile As String

Dim lngRow As Long

Dim intPos As Integer

Dim wbkEmployee As Workbook

Dim strSource As String

' Find the path to the folder

strFolder = ThisWorkbook.FullNameURLEncoded

intPos = InStrRev(strFolder, "\")

strFolder = Left(strFolder, intPos)

lngRow = 2

Do

ThisWorkbook.Sheets("Sheet1").UsedRange.Copy

strFile = strFolder &
ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 1).Value

On Local Error Resume Next

Set wbkEmployee = Application.Workbooks.Open(strFile, False,
False)

If Err.Number > 0 Then

ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 2).Value =
"In use"

Else

wbkEmployee.Sheets("Sheet1").Select

wbkEmployee.Sheets("Sheet1").Range("A1").Select

wbkEmployee.Sheets("Sheet1").Paste

ThisWorkbook.Worksheets("Sheet2").Cells(lngRow, 2).Value =
"Updated"

wbkEmployee.Close True

End If

lngRow = lngRow + 1

Loop Until IsEmpty(ThisWorkbook.Worksheets("Sheet2").Cells(lngRow,
1))

Application.CutCopyMode = False

End Sub

'---- End of listing

If you copy this and get some red lines in you code just concatenate
those to a single line.

HTH,

Wouer
 | 
Pages: 1
Prev: Autocomplete doesn't function properly
Next: Get Column Header Value