Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Excel macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
Tim Tim is offline
external usenet poster
  
Posts: 408
Default Excel macro
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






Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 134
Default Excel macro
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






.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort Gavin Excel Worksheet Functions 0 May 17th 07 01:20 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 1 February 5th 07 09:31 PM
My excel macro recorder no longer shows up when recording macro jack Excel Discussion (Misc queries) 3 February 5th 07 08:22 PM
Excel Macro Issue Trying to autorun Macro Upon Opening Worksheet wyndman Excel Programming 2 May 25th 04 06:59 PM


All times are GMT +1. The time now is 11:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement