Macro for copy

[Frank Situmorang]

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.

 

[Joel]

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]

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,

 

[Joel]

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]

Joel,

Thank you for your response, but how can we do it without looping because
the workbook P range A1...D3 is already fixed ( will not change)

And workbooked Q is always replication of the 3 lines ( range of P), so what
we want is to xldown the column A ( in this matter count will function to
know numbers of lines, then we copy range A1...D3 of Workbook P, to workbook
Q range B down to the result of counts).

I appreciate your help.

 

[Frank Situmorang]

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
are:
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

 

[Joel]

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("A13").Copy
With Hist_sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & (LastRow + 1)).Paste
End With
End Sub

 

[Frank Situmorang]

Joel:

Thanks for your code, I have tried it but it hang on the "FiletoOpen".

I need to explain that the formula is in the Historical price, actually in
this workbook there a sheet of accumulation of all p.o lines, but the
important sheet to copy containing formula is P.O Sheet name " P.O New " with
the rang AW12..CB60

This will be copied to all P.O files, the purpose of the formula is to
convert p.o line into sideway price list

Pls. also note that the clerck already put the number in colum AV12 down
until the last P.O typed by the clreck ( could change as the new P.O will be
typed) whle the formula to be put to column AW ( Not AV), that is why I
changed your suggestion rage.

I appeciate your help on how to solve the problem because it stops in the
"filetoOpen"

Thanks in advance

Frank

 

[Joel]

the error is being cause by the chdir directory

Const POBook_dir = "C:\temp"

change statement to a real directory on you PC.

You probably want to put in an actual sheet name instead of the Activesheet

from
Set PO_sht = PO_bk.ActiveSheet
to
Set PO_sht = PO_bk.sheets("P.O New")

Make sure you include all the blank spaces. Your posting has a space after
the word new. I don't know if that is a typo or is really the sheet name.