Find the location on spreadsheet B and copy/paste data from spreadsheet A

[txfrazier]

Hi, I'm a newbie to the forum and I'm trying to write a macro that will find the location where: Columns = Dates and Rows = Employee Names and I need to paste the % of the day the employee is working from another spreadsheet. Probably best if I illustrate


On spreadsheet A: I have the value 50%. This represents the amount of time Employee C will work today.

[pre]

On spreadsheet B: I have a "Table" (Not defined as a table)

a             b           c           d
1                                        Today
2              2/4/13        2/5/13      2/6/13      2/7/13
3 Employee A   100%          100%        100%        100%
4 Employee B   100%          100%        100%        100%
5 Employee C   100%          100%        50%         100%
6 Employee D   100%          100%        100%        100%
7 Employee E   100%          100%        100%        100%

[/pre]

I can find the location of "Today" and "Employee C" easy enough. But how would I find the intersecting cell location "C5"? More imortantly, how would I copy the value "50%" from Spreasheet A to Spreadsheet B "C5"?


Thanks in advance for your help. Sorry if my logic is hard to follow. Also, I couldn't get my columns to line up in the illustration above.


Thomas

 

[Hui]

Thomas


Are the worksheets in the same workbook or different workbooks?


When pasting tables, put a single ` (next to 1 Key under the Esc key) in front of and after the table

 

[txfrazier]

They are in two separate workbooks. The macro will open workbook B and paste the value from workbook A into the correct cell location. Thanks for your help.

 

[McPiffle]

Thomas, try something like this:


employee = "A3"

workdate = "C1"

worktime = "50%"


Set r = Range(employee)

Set c = Range(workdate)


Cells(r.Row, c.Column) = worktime


Of course, substitute employee, workdate and worktime for whatever variable names you've used in your VBA code. The code assumes that spreadsheet B is the active spreadsheet.


Hope this helps.


P.S. I'm a newbie to the forum, too, and a newbie to VBA programming, though I used to program in COBOL in a previous life

 

[Dhamo]

Try this.


'Write 50% in A1 and save the workbook as 'wb2.xlsx'

Sub Update_txfrazier()

Dim strcurrentdate As Date


strcurrentdate = Format(Now, "M/D/YYYY")


Workbooks.Open ("C:UsersDhamoDesktopwb2.xlsx")

Worksheets("source").Select

Range("a1").Copy 'Write 50% in A1 and save the workbook as 'wb2.xlsx'

Workbooks.Open ("C:UsersDhamoDesktopwb1.xlsx")

For i = 3 To 31 'assuming ttl month days

'MsgBox Cells(2, i).Value

If Cells(2, i) = strcurrentdate Then

desCol = i

Exit For

End If

Next

For j = 2 To 101 'assuming ttl employee list is 100

If Cells(j, 2) = "Employee C" Then

desRow = j

Exit For

End If

Next

ActivateWB ("wb2.xlsx")

Worksheets("source").Select

Range("a1").Copy

ActivateWB ("wb1.xlsx")

Cells(j, i).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

MsgBox "hi.. Done..!!", vbOKOnly, "Aaja aaja smile"

End Sub

Function ActivateWB(wbname As String)

Workbooks(wbname).Activate

End Function

 

[Dhamo]

Here you go..! The sample files.. Save it in a folder.. and change the location names in your code..


http://www.2shared.com/file/5VOtJmxi/txfrazier.html

http://www.2shared.com/file/SThWtuEe/Wb1.html

http://www.2shared.com/file/8B3toqhd/Wb2.html