ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Macro/coyy & Paste from a new data a fix infp (https://www.excelbanter.com/excel-programming/420914-macro-coyy-paste-new-data-fix-infp.html)

bbmexgal
Macro/coyy & Paste from a new data a fix infp
 
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

JLGWhiz
Macro/coyy & Paste from a new data a fix infp
 
What causes the source file name to change and how do you now know what file
name to use when you access it? You cannot just throw a variable out there
without some path to the source and the logic that creates it.

"bbmexgal" wrote:

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

bbmexgal
Macro/coyy & Paste from a new data a fix infp
 
my source name i.e (Inv-HRDP-090100-QWsupp5.xls") is a expense report and
changes every month and have 150 accounts that report. is there a way that I
can use a wild card or activesheet?. I'm trying to simplify data entry in the
invoicelog.xls file.

thanks.



--
bb


"JLGWhiz" wrote:

What causes the source file name to change and how do you now know what file
name to use when you access it? You cannot just throw a variable out there
without some path to the source and the logic that creates it.

"bbmexgal" wrote:

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

JLGWhiz
Macro/coyy & Paste from a new data a fix infp
 
You didn't really read my post did you? There is no way for anyone to
suggest how to handle the problem unless you can tell us something about what
makes the source file name change and if all of it changes or only part of it
and is there a logical pattern to the change like the date, next consecutive
number or what?

"bbmexgal" wrote:

my source name i.e (Inv-HRDP-090100-QWsupp5.xls") is a expense report and
changes every month and have 150 accounts that report. is there a way that I
can use a wild card or activesheet?. I'm trying to simplify data entry in the
invoicelog.xls file.

thanks.



--
bb


"JLGWhiz" wrote:

What causes the source file name to change and how do you now know what file
name to use when you access it? You cannot just throw a variable out there
without some path to the source and the logic that creates it.

"bbmexgal" wrote:

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

JLGWhiz
Macro/coyy & Paste from a new data a fix infp
 
To shorten the code lines, you could use something like:

Set srcSht = Workbooks(Inv-HRDP-090100=QWsupp5.xls)

Then, thereafter, you would just use the srcSht to refer to the file. Maybe
that was what your were asking.



"bbmexgal" wrote:

my source name i.e (Inv-HRDP-090100-QWsupp5.xls") is a expense report and
changes every month and have 150 accounts that report. is there a way that I
can use a wild card or activesheet?. I'm trying to simplify data entry in the
invoicelog.xls file.

thanks.



--
bb


"JLGWhiz" wrote:

What causes the source file name to change and how do you now know what file
name to use when you access it? You cannot just throw a variable out there
without some path to the source and the logic that creates it.

"bbmexgal" wrote:

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


All times are GMT +1. The time now is 05:06 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com