Macro/coyy & Paste from a new data a fix infp

[bbmexgal]

Hello,

Here is the macro I created, my problem is that the file source is changing
names all the time I try to use active sheet but is not working. Do you have
a suggestion?

Sub invoices()
'
' invoices Macro
' Macro recorded 12/5/2008 by bb
'
' Keyboard Shortcut: Ctrl+w
'
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Range("C12").Select
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Range("C2778").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Range("C7").Select
Application.CutCopyMode = False
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Range("D2778").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("E2778").Select
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Range("C9").Select
Application.CutCopyMode = False
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Range("C13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Range("F2778").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
Windows("2008 Invoice Log.XLS").Activate
Range("H2778").Select
Windows("Inv-HRDP-090100-QWsupp5.xls").Activate
ActiveWindow.SmallScroll Down:=15
Range("L78").Select
Application.CutCopyMode = False
Selection.Copy
Windows("2008 Invoice Log.XLS").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub



--
bb

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f65a360&dg=microsoft.public.excel.programming

 

[Joel]

Try something like this. Your first Pastespecial didn't have a range so I
put A1


Sub invoices()
'
' invoices Macro
' Macro recorded 12/5/2008 by bb
'
' Keyboard Shortcut: Ctrl+w

Sourcefile = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If Sourcefile = False Then
MsgBox ("Cannot open file - Exit macro")
Exit Sub
End If
Set Srcbk = Workbooks.Open(Filename:=Sourcefile)
Set SrcSht = Srcbk.Sheets("Sheet1")

Destfile = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If Destfile = False Then
MsgBox ("Cannot open file - Exit macro")
Exit Sub
End If
Set DestBk = Workbooks.Open(Filename:=Destfile)
Set DestSht = DestBk.Sheets("Sheet1")

'
With SrcSht
.Range("C12").Copy
DestSht.Range("A1").PasteSpecial _
Paste:=xlPasteValues
Application.CutCopyMode = False

.Range("C2").Copy
DestSht.Range("C2778").PasteSpecial _
Paste:=xlPasteValues

.Range("C7").Copy
DestSht.Range("D2778").PasteSpecial _
Paste:=xlPasteValues
End With
End Sub