copy & paste between 2cells on 2shts

[Todd]

I need to do a 8,000 line manual cut & paste project to
build a bridge between 2 systems.

I will have 2 worksheets open and will have them arranged
horizontally.

I will look for match then need to copy and paste one
cells contents from one sheet to the other.

later I will do a vlookup to pull in several columns of
datavia this manually pasted id code.

Can I get a macro to copy and paste between the 2
different sheets after I click to the right cells.

Thanks for the time.

Todd

 

[Frank Kabel]

Hi todd
what do you mean with 'look for a match'.

 

[Tom Ogilvy]

Selection.Copy Destination:=Worksheets("Sheet2").Range("A1")

Assumes the selections are all in one column.

 

[Todd]

I have 2 sheets open and split horizontally on
screen "look for match" means I visually looking from one
saheet to the other and finding correct business entity to
paste my id code to.

It is highly manul and I want to minimize my key strokes
and mouse clicks.

I will have clicked to the cell on sheet 1 where I need to
paste data - then I will click to sheet 2 and click on the
correct code I need to paste to sheet 1 from sheet 2.

Then I hit cntrl-z and bang cell form sheet 2 copied to
sheet 1.

 

[Todd]

Hi Tom
I could not get it to work in either direction

Sub testcopybetweentwo()

Selection.Copy Destination:=Worksheets("book2").Range
("A1")

End Sub

I tired copying form book 1 to book 2 and from book 2 to
book 1

error said: run time error 9 subscript out of range

see reply to Frank for clarification

 

[Frank Kabel]

Hi Todd
how do you match these entries. sound like they have a common code. You
may be able to use VLOOKUP for this procedure. Can you post some
example rows of both sheets and your expected result (plain text - no
attachments please)

 

[Tom Ogilvy]

Well, you didn't say they were in separate workbooks - and then, given that
you know that they are, you didn't adjust the code to take into account
separate workbooks.

Assume Book1.xls, Sheet1, A1:A200 is the source
Book2.xls, Sheet3, B9 is the destination

Workbooks("Book1.xls").Worksheets("Sheet1").Range("A1:A200").Copy _
Destination:=Workbooks("Book2.xls").Worksheets("Sheet3").Range("B9")

if you are using new default workbooks, then leave out the .xls extenstion,
since they don't have an extension. If the workbooks have ever been saved,
then include the name and the extension.

 

[todd]

welcome to my punishment

no vlookup possible

have 2 sheets

sheet 1 has a 6 digit code and a hospital name

need to find hospitsal name on sheet 2 and then cut and
paste 6 digit code.

thereis allot of interpetation to the names between shet
one and sheet 2

I do have a partial common denominator in tat the 5 digit
zip is on both but that has play in it.

No matter how you slice it - I am looking at one heck of a
manul project.

Any comments are welcome.

 

[Frank Kabel]

welcome to my punishment

no vlookup possible

have 2 sheets

sheet 1 has a 6 digit code and a hospital name

need to find hospitsal name on sheet 2 and then cut and
paste 6 digit code.

thereis allot of interpetation to the names between shet
one and sheet 2

I do have a partial common denominator in tat the 5 digit
zip is on both but that has play in it.

No matter how you slice it - I am looking at one heck of a
manul project.

Any comments are welcome.

Hi
can you provide some example data. Maybe there's a way using something
like the following array formula (entered with CTRL+SHIFT+ENTER)
=INDEX('sheet2'!$B$1:$B$1000,MATCH(TRUE,ISNUMBER(FIND("hospital_name",'
sheet2'!$A$1:$A$1000)),0))

This will search column A of sheet 2 for a cell which contains (has not
to be an exact match) the hospital name and return the corresponding
value from column B

Frank

 

[Todd]

Sub test_copy_between3()

Workbooks("Bk1.xls").Worksheets("Sheet1").Range
("A1:A200").Copy _
Destination:=Workbooks("Bk2.xls").Worksheets
("Sheet1").Range("B9")

End Sub

Thanks tom is worked accept one big problem - I need to go
to an ever changing selected cell in bk2 sheet1

You have it going to a specific cell "b9"

I am goin to be looking for match of names for example
between bk1 & bk2 then I wantr to click on the cell of the
id in bk1 sheet 1 & ckick on target cell in bk2 ssheet 2
and hot the "cntrl Z key and kaboom - cut and paste
bewtween the 2 active cells ( 1 on each sheet in each bk.

 

[Todd]

I Frank thanks for the interest - also see latest reply to
Tom

We do not have an exact match but will keep in mind for
when we do

I will double check data tomarrow incase I am missing
something and will post tuesday.

Thanks Again

 

[Tom Ogilvy]

So you want to manually make a selection each workbook, then run the macro.

Here is the macro. Worked for me regardless of which workbook was active at
the time.

Sub CopySelections()
Windows("Bk1.xls").ActiveCell.Copy _
Destination:=Windows("Bk2.xls").ActiveCell
End Sub

If you want more than one cell copied from bk1.xls
Sub CopySelections()
Windows("Bk1.xls").ActiveCell.Resize(1,10).Copy _
Destination:=Windows("Bk2.xls").ActiveCell
End Sub

would copy the activecell and 9 cells to the right. Adjust to suit.

 

[Todd]

Thank you much it works great

now all I need is a macro to fixed a totally screwed up
corp system !