PASTE FUNCTION IN MACRO

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
Does anyone know why this paste function will not work.

Sub ADD()
Workbooks.Open Filename:= _
"C:\PDF FILES\INVOICE TRACKER\Invoice tracker.Xls"
Sheets("2005 INVOICES").Select
Columns("A").Find(ActiveCell.Value).Select
Selection.Copy
ActiveWorkbook.Save
ActiveWindow.Close
Range("F1").Select
ActiveSheet.Paste



End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
This should fix you up.

Sub ADD()
Dim PS as worksheet, SS as worksheet

Workbooks.Open Filename:= _
"C:\PDF FILES\INVOICE TRACKER\Invoice tracker.Xls"

set ss=Activesheet
Workbooks.add
set PS = activesheet
ss.activate

Sheets("2005 INVOICES").Select
Columns("A").Find(ActiveCell.Value).Select

Selection.Copy Ps.range("F1")
ActiveWorkbook.close true
End Sub

HTH
Cal
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
this function works great, but it puts the data into a new book instead of the invoice sheet that I want to put it into
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Code:
Sub ADD() 
Dim PS as worksheet, SS as worksheet, wb as workbook

Workbooks.Open Filename:= _ 
"C:\PDF FILES\INVOICE TRACKER\Invoice tracker.Xls" 
set ss=Activesheet 
set wb = workbooks("Invoice.xls")
wb.activate
set ps = wb.sheets("Invoice")
ss.activate 
Sheets("2005 INVOICES").Select 
Columns("A").Find(ActiveCell.Value).Select 

Selection.Copy Ps.range("F1") 
ActiveWorkbook.close true 
End Sub

These two lines will need to be updated with your values.

set wb = workbooks("Invoice.xls")
set ps = wb.sheets("Invoice")

Cal
 

JOSTERBAUER

Board Regular
Joined
Jan 17, 2005
Messages
101
Thanks that worked great. I forgot to change the 2 lines. I saw that just after I sent that to you. Thanks again for the great information
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
JOSTERBAUER,
In repsonse to your PM. I don't see anything in the code that would cause the Win XP to select the wrong cell in the find function. I run with XL2003, and have never really worked with XP, although I can't see the find function finding the cell under the the criteria cell versus the correct cell. Anyone out there aware of any issues with the XP .Find function?

Cal
 

Forum statistics

Threads
1,148,396
Messages
5,746,447
Members
424,020
Latest member
LongDoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top