Macro for Copying

[Frank Situmorang]

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

 

[Dave Peterson]

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]

Thank you Dave for your kind explanation
My macro stopped here:
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.

 

[Dave Peterson]

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?

Thank you Dave for your kind explanation
My macro stopped here:
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.

 

[Frank Situmorang]

Dave,

The error message is there is a box says " Run time error (9) subscript out
of range, then when I press on debug It shows this VBA in yellow highlighted.
Set DestCell = .Cells(.Rows.Count, "AV12").End(xlUp).Offset(1, 0)

I appreciate your help

 

[Dave Peterson]

I didn't notice a typo:
Set DestCell = .Cells(.Rows.Count, "AV").End(xlUp).Offset(1, 0)

Dave,

The error message is there is a box says " Run time error (9) subscript out
of range, then when I press on debug It shows this VBA in yellow highlighted.
Set DestCell = .Cells(.Rows.Count, "AV12").End(xlUp).Offset(1, 0)

I appreciate your help

 

[Dave Peterson]

And after your change:

Set DestCell = .Cells(.Rows.Count, "AW").End(xlDown).Offset(1, 0)

 

[Frank Situmorang]

Dave,

It has not worked as I want it, can I send you my worksheet?

 

[Dave Peterson]

No thanks.

You'd have to explain the problem anyway. Why not do it in the newsgroups where
you have lots of eyes looking for a solution.

But if you want to share the workbook with someone, you could put it on a site
like www.savefile.com and post the link in your message.

Maybe someone else will open your file.

Dave,

It has not worked as I want it, can I send you my worksheet?

--
H. Frank Situmorang

I didn't notice a typo:
Set DestCell = .Cells(.Rows.Count, "AV").End(xlUp).Offset(1, 0)

 

[Frank Situmorang]

Thanks Dave, you are right that many ayes could see...if I put it here, now
let me try to put the file on the website that you said.

The file where the fixed range to be copied is attached on:
http://www.savefile.com/files/1347473

The clerck will always type the number on the column AV, to make sure the
pattern will alwasy be the same, but the range to be copied is colum AW down
to the last number on column AV. So Xldown can be used on AV.

This is what I do not know how to make it

I do not attached the workbook of fixed range because it too big in size,
but I think it is not so difficult to understand because I already mentioned
the fixed range.

The problem is that column AV is dynamic, as the clercks will keep typing
the Purchase Order if there is a new purchase.

Thanks Dave for your help.

--
H. Frank Situmorang

No thanks.

You'd have to explain the problem anyway. Why not do it in the newsgroups where
you have lots of eyes looking for a solution.

But if you want to share the workbook with someone, you could put it on a site
like www.savefile.com and post the link in your message.

Maybe someone else will open your file.

Dave,

It has not worked as I want it, can I send you my worksheet?