| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for Copying Data
I have two spreadsheets. I need to copy a cell from one spreadsheet,
go to the other spreadsheet and find the number in that one and then copy the contents of another cell back to the original spreadsheet. Here is the code I have which works but requires some hands on copying and pasting. Dim strToFind As String Selection.Copy strToFind = InputBox("Enter the code to find") ' TRIED TO GET THE ITEM I COPIED TO PASTE BUT I MANUALLY HAVE TO HIT CTRL + V TO PASTE INTO INPUT BOX AND THEN CLICK OK. I WANT THIS STEP AUTOMATED. 'Selection.Paste Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate Columns("L:L").Select Selection.Find(what:=strToFind, After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, -3).Select Selection.Copy Windows("2005 beginningbalancesglpost2.fil.xls").Activate ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Any other suggestions would be greatly appreciated. I recorded this macro and then started tweaking it. I am open to any and all suggestions. Thank you. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Copying Data
How about assigning the value from the old cell to a temp variable then
assigning the temp variable to the new cell. TempValue = Sheet1.Cells(1,1).Value Sheet2.Cells(1,1).Value = TempValue The above will copy the value from A1 on sheet1 to A1 on sheet2. -- Rui "clk" wrote in message ... I have two spreadsheets. I need to copy a cell from one spreadsheet, go to the other spreadsheet and find the number in that one and then copy the contents of another cell back to the original spreadsheet. Here is the code I have which works but requires some hands on copying and pasting. Dim strToFind As String Selection.Copy strToFind = InputBox("Enter the code to find") ' TRIED TO GET THE ITEM I COPIED TO PASTE BUT I MANUALLY HAVE TO HIT CTRL + V TO PASTE INTO INPUT BOX AND THEN CLICK OK. I WANT THIS STEP AUTOMATED. 'Selection.Paste Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate Columns("L:L").Select Selection.Find(what:=strToFind, After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, -3).Select Selection.Copy Windows("2005 beginningbalancesglpost2.fil.xls").Activate ActiveCell.Offset(0, 1).Select ActiveSheet.Paste Any other suggestions would be greatly appreciated. I recorded this macro and then started tweaking it. I am open to any and all suggestions. Thank you. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Copying Data
On Oct 29, 1:50*pm, "ruic" wrote:
How about assigning the value from the old cell to a temp variable then assigning the temp variable to the new cell. TempValue = Sheet1.Cells(1,1).Value Sheet2.Cells(1,1).Value = TempValue The above will copy the value from A1 on sheet1 to A1 on sheet2. -- Rui "clk" wrote in message ... I have two spreadsheets. *I need to copy a cell from one spreadsheet, go to the other spreadsheet and find the number in that one and then copy the contents of another cell back to the original spreadsheet. Here is the code I have which works but requires some hands on copying and pasting. * Dim strToFind As String * *Selection.Copy * *strToFind = InputBox("Enter the code to find") * ' TRIED TO GET THE ITEM I COPIED TO PASTE BUT I MANUALLY HAVE TO HIT CTRL + V TO PASTE * *INTO INPUT BOX AND THEN CLICK OK. *I WANT THIS STEP AUTOMATED. * *'Selection.Paste * *Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate * *Columns("L:L").Select * *Selection.Find(what:=strToFind, After:=ActiveCell, LookIn:= _ * * * *xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ * * * *xlNext, MatchCase:=False, SearchFormat:=False).Activate * *ActiveCell.Offset(0, -3).Select * *Selection.Copy * *Windows("2005 beginningbalancesglpost2.fil.xls").Activate * *ActiveCell.Offset(0, 1).Select * *ActiveSheet.Paste Any other suggestions would be greatly appreciated. *I recorded this macro and then started tweaking it. *I am open to any and all suggestions. Thank you.- Hide quoted text - - Show quoted text - I tried changing it but then when it runs it has TempValue = true. I will need to run this many times on one spreadsheet. I can't assign a specific cell in the code. Dim TempValue As String TempValue = Selection.Copy Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate Columns("L:L").Select Selection.Find(what:=TempValue, After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, -3).Select Selection.Copy Windows("2005 beginningbalancesglpost2.fil.xls").Activate ActiveCell.Offset(0, 1).Select ActiveSheet.Paste |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro for Copying Data
On Oct 29, 2:08*pm, clk wrote:
On Oct 29, 1:50*pm, "ruic" wrote: How about assigning the value from the old cell to a temp variable then assigning the temp variable to the new cell. TempValue = Sheet1.Cells(1,1).Value Sheet2.Cells(1,1).Value = TempValue The above will copy the value from A1 on sheet1 to A1 on sheet2. -- Rui "clk" wrote in message .... I have two spreadsheets. *I need to copy a cell from one spreadsheet, go to the other spreadsheet and find the number in that one and then copy the contents of another cell back to the original spreadsheet. Here is the code I have which works but requires some hands on copying and pasting. * Dim strToFind As String * *Selection.Copy * *strToFind = InputBox("Enter the code to find") * ' TRIED TO GET THE ITEM I COPIED TO PASTE BUT I MANUALLY HAVE TO HIT CTRL + V TO PASTE * *INTO INPUT BOX AND THEN CLICK OK. *I WANT THIS STEP AUTOMATED. * *'Selection.Paste * *Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate * *Columns("L:L").Select * *Selection.Find(what:=strToFind, After:=ActiveCell, LookIn:= _ * * * *xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ * * * *xlNext, MatchCase:=False, SearchFormat:=False).Activate * *ActiveCell.Offset(0, -3).Select * *Selection.Copy * *Windows("2005 beginningbalancesglpost2.fil.xls").Activate * *ActiveCell.Offset(0, 1).Select * *ActiveSheet.Paste Any other suggestions would be greatly appreciated. *I recorded this macro and then started tweaking it. *I am open to any and all suggestions. Thank you.- Hide quoted text - - Show quoted text - I tried changing it but then when it runs it has TempValue = true. *I will need to run this many times on one spreadsheet. *I can't assign a specific cell in the code. Dim TempValue As String * * TempValue = Selection.Copy * * Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate * * Columns("L:L").Select * * Selection.Find(what:=TempValue, After:=ActiveCell, LookIn:= _ * * * * xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ * * * * xlNext, MatchCase:=False, SearchFormat:=False).Activate * * ActiveCell.Offset(0, -3).Select * * Selection.Copy * * Windows("2005 beginningbalancesglpost2.fil.xls").Activate * * ActiveCell.Offset(0, 1).Select * * ActiveSheet.Paste- Hide quoted text - - Show quoted text - I got it working. Thanks for the help. Here is the code that works. Dim TempValue As String Selection.Copy TempValue = Selection Windows("Full GL Mapping Oracle to SOS EDITED.xls").Activate Columns("L:L").Select Selection.Find(what:=TempValue, After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(0, -3).Select Selection.Copy Windows("2005 beginningbalancesglpost2.fil.xls").Activate ActiveCell.Offset(0, 1).Select ActiveSheet.Paste |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| A better Macro for Copying Data | Excel Programming | |||
| macro error - copying data twice | Excel Discussion (Misc queries) | |||
| Macro Help - Copying and appending data | Excel Worksheet Functions | |||
| Macro Help - copying specific data | Excel Programming | |||
| Copying data ranges in a macro | Excel Programming | |||
Linear Mode
