ExcelBanter - Excel macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Excel macro (https://www.excelbanter.com/excel-programming/312140-excel-macro.html)

I am a basic excel user and wish to populate one excel sheet("Completions LL
Register 2004-09-030.xls") with another sheet that arives to me on
occasion(LL-CP-0163.xls). The trouble i am having is trying to figure out how
to make the macro open the next file (ie LL-CP-0164.xls) copy the same cell
references and then paste them in ("Completions LL Register 2004-09-030.xls")
only one row down as to not overwrite the previous data. Any help is
appreciated and some sample code is below to help explain.

Windows("Completions LL Register 2004-09-030.xls").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("F3:H3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("F164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B12:J18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("G164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("F2:H2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("H164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B5:D5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("I164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
ActiveWindow.SmallScroll Down:=-27
Range("B20:J25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("L164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B35:J39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("O164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B27:J33").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("O164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B35:J39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("Q164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
ActiveWindow.SmallScroll Down:=12
Range("B41:J46").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-030.xls").Activate
Range("R164").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E164").Select
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
"\\SNF001\SNF1VOL3\SHARED\INTL\ 9000 Field Files\ 6 Lessons Learned
- IRR's\Completion 2004\Lessons Learned Close-Out Form\LL-CP-0163.xls"

End Sub

Tim,

There are two ways I can think of. One I know will work
and the other I am not sure about.

First, the way I know will work is to use the
GetOpenFilename method of Application object.The code
would look something like this:

dim fileName as variant

fileName=application.GetOpenFilename("Excel Files(*.xls),
*.xls")

You would then use the fileName variable to open the file.

The other way I can think might work is to not specify
the exact file name. Use a wildcard in the file name
(i.e. LL-CP*.xls). If you did this you would have to
ensure that there was only one file in the directory your
opening the file from. Move the file as soon as you
accomplish this. You could do this programatically with
the filecopy method. I hope this helps!

Kevin
-----Original Message-----
I am a basic excel user and wish to populate one excel
sheet("Completions LL
Register 2004-09-030.xls") with another sheet that
arives to me on
occasion(LL-CP-0163.xls). The trouble i am having is
trying to figure out how
to make the macro open the next file (ie LL-CP-0164.xls)
copy the same cell
references and then paste them in ("Completions LL
Register 2004-09-030.xls")
only one row down as to not overwrite the previous data.
Any help is
appreciated and some sample code is below to help
explain.

Windows("Completions LL Register 2004-09-
030.xls").Activate
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("F3:H3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("F164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B12:J18").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("G164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("F2:H2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("H164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B5:D5").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("I164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
ActiveWindow.SmallScroll Down:=-27
Range("B20:J25").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("L164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B35:J39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("O164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B27:J33").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("O164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
Range("B35:J39").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("Q164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Windows("LL-CP-0163.xls").Activate
ActiveWindow.SmallScroll Down:=12
Range("B41:J46").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Completions LL Register 2004-09-
030.xls").Activate
Range("R164").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E164").Select
Application.CutCopyMode = False
ActiveSheet.Hyperlinks.Add Anchor:=Selection,
Address:= _
"\\SNF001\SNF1VOL3\SHARED\INTL\ 9000 Field
Files\ 6 Lessons Learned
- IRR's\Completion 2004\Lessons Learned Close-Out
Form\LL-CP-0163.xls"

End Sub

.