If there's no data under AW12, then xldown will take you to the last cell in
that column. Trying to go down one more row won't work.

Is that possible?

But that's a guess. What was the error message that occurred?

Frank Situmorang wrote:

Thank you Dave for your kind explanation
My macro stopped he
Set DestCell = .Cells(.Rows.Count, "AW12").End(xlDown).Offset(1, 0)

and this is my revised VBA:
Sub Frankcopy2()
Dim RngToCopy As Range
Dim DestCell As Range

With Workbooks("historical actual material pricebased on
PO.xls").Worksheets("PO New")
Set RngToCopy = .Range("AW12:CB60")
End With

With Workbooks("M10-7-004 DNP (2).xls").Worksheets("PO New (2)")
'right after the last used cell in column AW
Set DestCell = .Cells(.Rows.Count, "AW12").End(xlDown).Offset(1, 0)
End With

RngToCopy.Copy _
Destination:=DestCell

RngToCopy.Copy
DestCell.PasteSpecial Paste:=xlPasteFormulas

End Sub

I think it triggerred my mind, but I need more your explanantion for the
following:

1. the colum that has number is column AV, start from AV12, therefore the
copy to should always start from AW12, that is why I tried to change it to
excel down, but the offset maybe wrong in order count to work correctly.

2. The name of the file could be change, how can we alwasy change the name
of the file in VBA, can we just use file to open and then after finished,
closed and save the file, then we run again the macro and open another file,
what is the VBA for this.

We just use the macro file open and the file from which the formula open. In
my example there are 3 files open
1. my macro Frankcopy
3. the base file " Historical material purchased pricdebased on P.O
4. P.O file which consists of many files

Thanks again for your help.
--
H. Frank Situmorang

"Dave Peterson" wrote:

For the next step of your macro, I'm gonna assume that both workbooks are open.
(That can be added later if need be.)

Option Explicit
Sub FrankCopy2()
dim RngToCopy as range
dim DestCell as range

with workbooks("historical actual material pricebased on PO.xls")
.worksheets("somesheetnamehere")
set rngtocopy = .range("AW12:CB60")
end with

with workbooks("M10-7-004 DNP (2).xls").worksheets("somesheetnamehere")
'right after the last used cell in column AW
set destcell = .cells(.rows.count,"AW").end(xlup).offset(1,0)
end with

rngtocopy.copy _
destination:=destcell

end with

You may want:

rngtocopy.copy
destcell.pastespecial paste:=xlpasteformulas


Frank Situmorang wrote:

Hello Sirs,

To be more clear the following is my VBA and my macro is in separate
workbook. Pls. see my comments which are the problems I encounterred. I made
the macro by recording macro, actually I am not an expert in macro.

This is my VBA:
Sub Frankcopy()
'
' Frankcopy Macro
' Macro recorded 1/23/2008 by Frank
'
' Keyboard Shortcut: Ctrl+Shift+F
'Franks comment, below is the workbook on sheet "PO New" and the range is
already fixed
'to be copied to many workbooks, What is the VBA to open the file, here I
opened it first
Windows("historical actual material pricebased on PO.xls").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("PO New").Select
Range("AW12:CB60").Select
Selection.Copy
'below is where the rage to be copied to, but what is the VBA to open
many files
' with the same Sheet name"PO new" and then close it after finish
performing copying
Windows("M10-7-004 DNP (2).xls").Activate
' below is the range where the above patterned range to be copied, the
rage is variable
'could go down upto so many lines with the pattern ( number of lines of
P.O) is the same
'could you teach me the VBA for this?, FOR YOUR INFO ON colum AV 12 down
there is number
'where we can use End.xlDown, but I do not know how to make it as the
range is to be
' copied to colum AW (After AV)
Range("AW12:AW60").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub

Thanks in advance
--
H. Frank Situmorang

--

Dave Peterson


--

Dave Peterson