Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------- 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/comm...el.programming |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro/coyy & Paste from a new data a fix infp
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 "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 ---------------- 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/comm...el.programming |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy & paste data | Excel Discussion (Misc queries) | |||
Copy and paste data using macro | Excel Programming | |||
Macro to paste data | Excel Discussion (Misc queries) | |||
Paste data with a macro? | Excel Programming | |||
Macro to paste data into next blank row | Excel Programming |