Sub Macro7()
'
' Macro7 Macro
' Macro recorded 2/20/2008 by Frank
'

Const POBook_dir = "C:\temp"

ChDir (POBook_dir)

fileToOpen = Application _
.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Historical Price File")
Workbook.Open Filename:=fileToOpen
Set Hist_bk = ActiveWorkbook
Set Hist_sht = Hist_bk.ActiveSheet

fileToOpen = Application _
.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="PO File")
Workbook.Open Filename:=fileToOpen
Set PO_bk = ActiveWorkbook
Set PO_sht = PO_bk.ActiveSheet



PO_sht.Range("A1:D3").Copy
With Hist_sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & (LastRow + 1)).Paste
End With
End Sub


"Frank Situmorang" wrote:

Joel:

Before we run the macro, we should open first these 3 files

1. workbook where the macro is recorded
2. Historical price...... from which the fixed range will be copied to
3. the Purchase order file, where the fixed range to be copied,

This is the macro that can already work by using the xldown, but my problem
a
the name of the file number 3 above is variable ( many Purchase order
workbooks that will be worked out by this macro) although I can ask them just
to do it one by one.

Therefore I have a problem in this VBA:
Windows("E10-7-014 - Y K K.xls").Activate
Since other name could be "E20-8-001-Yamaha",....

I appreciate your help.

Frank

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 2/20/2008 by Frank
'

'
Windows("E10-7-014 - Y K K.xls").Activate
Columns("AW:CB").Select
Selection.ClearContents
Range("AW12").Select
ActiveCell.FormulaR1C1 = "a"
Range("AV12").Select
Selection.End(xlDown).Select
Range("AW844").Select
Range(Selection, Selection.End(xlUp)).Select
Windows("historical actual material pricebased on PO.xls").Activate
Selection.Copy
Windows("E10-7-014 - Y K K.xls").Activate
ActiveSheet.Paste

End Sub

--
H. Frank Situmorang


"Joel" wrote:

the worksheet names need to be corrected as shown in the code below. Also
the directory c:\temp need to be modified.


Sub test()
Const POBook_dir = "C:\temp"

chdir (POBook_dir)

fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")

Workbook.Open Filename:=fileToOpen
Set POBk = ActiveWorkbook

With ThisWorkbook.Worksheets("Put in worksheet name") '<= change
RowCount = 1
Do While .Range("A" & RowCount) < ""
PONumber = .Range("A" & RowCount)

With POBk.Sheets("sheet1")
'<=change name
Set c = .Columns("A:A").Find(what:=PONumber, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
Set CopyRange = .Range(.Range("A" & c.Row), _
.Range("D" & c.Row))
End If
End With
If Not c Is Nothing Then
CopyRange.Copy Destination:=.Range("B" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With
End Sub


"Frank Situmorang" wrote:

Joel,

Thanks for your suggestion, this is more workable I think, sorry I am not
too good in VBA, actually my expertise is an accountant, but I better have a
try.

Since I have a langguage problem in expressing my difficulties, actually
what I meant by Worksheet P should be a workbook P with sheet P.O new, the
range is always fixed ( never change)

Worksheet Q, I should have mentioned WorkbookQ ( this is example) actually
there are many workbooks and I want when we run the macro, we are prompted to
open the file than copy the fixed rage to valriable range ( dynamic range) in
this workbook.

Could you please help what should be changed in the VBA that you gave me?

With many thanks,
--
H. Frank Situmorang


"Joel" wrote:

Sub test()
Const POBook = "C:\temp\abc.xls"

Workbook.Open Filename:=POBook
Set POBk = ActiveWorkbook


With ThisWorkbook.Worksheets("Q")
RowCount = 1
Do While .Range("A" & RowCount) < ""
PONumber = .Range("A" & RowCount)

With POBk.Sheets("sheet1")
Set c = .Columns("A:A").Find(what:=PONumber, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
Set CopyRange = .Range(.Range("A" & c.Row), _
.Range("D" & c.Row))
End If
End With
If Not c Is Nothing Then
CopyRange.Copy Destination:=.Range("B" & RowCount)
End If
RowCount = RowCount + 1
Loop
End With
End Sub


"Frank Situmorang" wrote:

Hello,

I want to create a button macro to perform a routine coping, here is my
worksheet:

Worksheet P
A B C D
1
2
3
Worksheet Q
A B C D
1 1
2 2
3 3
4 4
5 5
6 6
.
.
Worksheet P 's range (A1..D3) to be copied is already fixed because this is
a patron ( containing formula)
Worksheet Q is the p.o line, could go down for thousand of line, but the
clerck is alaredy asked to have filled column A for (line nomber)

My question is how can we create a macro, to copy A1.D3 ( in another
workbook) to Purchase Order workbook for sheet P.O Sheet to line
B1...D.(variable)(could go down to many lines)

A1....down could be used to Cursor End. down, but I do not need to copy to
column A in worksheet Q, It should start from Column B in worksheet Q.

I appreciate for any idea provided.

--
H. Frank Situmorang