Thread: Copy from one workbook to another
View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Ole[_4_] Ole[_4_] is offline
external usenet poster
  
Posts: 7
Default Copy from one workbook to another
e up :-(

Here follows ALL my code, if prisliste_DK.xls all ready is open when i run
the code,
i works like a charm, if i put the code in a beforeclose event it works like
a charm,
but if i run it from a commandbutton it opens the prisliste_DK.xls and only
copies 76
cells out of 95 and onely 6 off them is correct ?
I have writen behind the code wisch cell is good or not with a YES or NO


Private Sub CommandButton1_Click()
Dim bClose As Boolean
Dim bk As Workbook
On Error Resume Next
Set bk = Workbooks("prisliste_DK.xls") 'sætter prisliste som til copy
On Error GoTo 0
If bk Is Nothing Then
bClose = True
Set bk = Workbooks.Open("C:\prisliste_udskrift\prisliste_DK .xls") ' YES
End If
Worksheets(2).Range("B20").Value = "4" ' kopier 4 farver til antal farver
YES
Worksheets(2).Range("N47").Value = Worksheets(2).Range("R1").Value ' sætter
pris pr designskift til 4 frv pris YES
bk.Worksheets(2).Range("D4").Value = _
Worksheets(2).Range("C2").Value ' kopier kunde NO
bk.Worksheets(2).Range("D5").Value = _
Worksheets(2).Range("I2").Value ' kopier opgave NO
bk.Worksheets(2).Range("D7").Value = _
Worksheets(2).Range("B11").Value NO & " MM " ' kopier bredde YES
bk.Worksheets(2).Range("D8").Value = _
Worksheets(2).Range("B14").Value ' kopier afsnit NO
bk.Worksheets(2).Range("C11").Value = "PRIS / " & _ YES
Worksheets(2).Range("B8").Value ' kopier salgsenhed NO
bk.Worksheets(2).Range("D26").Value = _
Worksheets(2).Range("B38").Value ' kopier Valuta NO
bk.Worksheets(2).Range("D6").Value = _
Format(Range("B47").Value) & " / " & Format(Range("B48").Value) ' kopier
folietype NO
bk.Worksheets(2).Range("D9").Value = _
Worksheets(2).Range("B23").Value & "%" ' kopier farvedækning NO
bk.Worksheets(2).Range("C12").Value = _
Worksheets(2).Range("E4").Value ' kopier Antal herfra og ned NO
bk.Worksheets(2).Range("C13").Value = _
Worksheets(2).Range("F4").Value NO
bk.Worksheets(2).Range("C14").Value = _
Worksheets(2).Range("G4").Value NO
bk.Worksheets(2).Range("C15").Value = _
Worksheets(2).Range("H4").Value NO
bk.Worksheets(2).Range("C16").Value = _
Worksheets(2).Range("I4").Value NO
bk.Worksheets(2).Range("C17").Value = _
Worksheets(2).Range("J4").Value NO
bk.Worksheets(2).Range("C18").Value = _
Worksheets(2).Range("K4").Value NO
bk.Worksheets(2).Range("C19").Value = _
Worksheets(2).Range("L4").Value NO
bk.Worksheets(2).Range("C20").Value = _
Worksheets(2).Range("M4").Value NO
bk.Worksheets(2).Range("C21").Value = _
Worksheets(2).Range("N4").Value NO
bk.Worksheets(2).Range("C22").Value = _
Worksheets(2).Range("O4").Value NO
bk.Worksheets(2).Range("C23").Value = _
Worksheets(2).Range("P4").Value ' Antal hertil NO
bk.Worksheets(2).Range("E12").Value = _
Worksheets(2).Range("E38").Value ' kopier 1 design 1-4 farver NO ONLY
SETS A "-" instead of a price?? THE REST IS NO
bk.Worksheets(2).Range("E13").Value = _
Worksheets(2).Range("F38").Value
bk.Worksheets(2).Range("E14").Value = _
Worksheets(2).Range("G38").Value
bk.Worksheets(2).Range("E15").Value = _
Worksheets(2).Range("H38").Value
bk.Worksheets(2).Range("E16").Value = _
Worksheets(2).Range("I38").Value
bk.Worksheets(2).Range("E17").Value = _
Worksheets(2).Range("J38").Value
bk.Worksheets(2).Range("E18").Value = _
Worksheets(2).Range("K38").Value
bk.Worksheets(2).Range("E19").Value = _
Worksheets(2).Range("L38").Value
bk.Worksheets(2).Range("E20").Value = _
Worksheets(2).Range("M38").Value
bk.Worksheets(2).Range("E21").Value = _
Worksheets(2).Range("N38").Value
bk.Worksheets(2).Range("E22").Value = _
Worksheets(2).Range("O38").Value
bk.Worksheets(2).Range("E23").Value = _
Worksheets(2).Range("P38").Value ' 1-4 farver hertil
bk.Worksheets(2).Range("F13").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value '
kopier 1-4 farver 2 design
bk.Worksheets(2).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value
bk.Worksheets(2).Range("F15").Value = _
Worksheets(2).Range("H38").Value + Worksheets(2).Range("H41").Value
bk.Worksheets(2).Range("F16").Value = _
Worksheets(2).Range("I38").Value + Worksheets(2).Range("I41").Value
bk.Worksheets(2).Range("F17").Value = _
Worksheets(2).Range("J38").Value + Worksheets(2).Range("J41").Value
bk.Worksheets(2).Range("F18").Value = _
Worksheets(2).Range("K38").Value + Worksheets(2).Range("K41").Value
bk.Worksheets(2).Range("F19").Value = _
Worksheets(2).Range("L38").Value + Worksheets(2).Range("L41").Value
bk.Worksheets(2).Range("F20").Value = _
Worksheets(2).Range("M38").Value + Worksheets(2).Range("M41").Value
bk.Worksheets(2).Range("F21").Value = _
Worksheets(2).Range("N38").Value + Worksheets(2).Range("N41").Value
bk.Worksheets(2).Range("F22").Value = _
Worksheets(2).Range("O38").Value + Worksheets(2).Range("O41").Value
bk.Worksheets(2).Range("F23").Value = _
Worksheets(2).Range("P38").Value + Worksheets(2).Range("P41").Value ' 1-4
2 design hertil
bk.Worksheets(2).Range("G14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 2)
' kopier 1-4 farver 3 design
bk.Worksheets(2).Range("G15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 2)
bk.Worksheets(2).Range("G16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 2)
bk.Worksheets(2).Range("G17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 2)
bk.Worksheets(2).Range("G18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 2)
bk.Worksheets(2).Range("G19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 2)
bk.Worksheets(2).Range("G20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 2)
bk.Worksheets(2).Range("G21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 2)
bk.Worksheets(2).Range("G22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 2)
bk.Worksheets(2).Range("G23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 2)
' 1-4 3 design hertil
bk.Worksheets(2).Range("H14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 3)
' kopier 1-4 farver 4 design
bk.Worksheets(2).Range("H15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 3)
bk.Worksheets(2).Range("H16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 3)
bk.Worksheets(2).Range("H17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 3)
bk.Worksheets(2).Range("H18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 3)
bk.Worksheets(2).Range("H19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 3)
bk.Worksheets(2).Range("H20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 3)
bk.Worksheets(2).Range("H21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 3)
bk.Worksheets(2).Range("H22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 3)
bk.Worksheets(2).Range("H23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 3)
' 1-4 4 design hertil
bk.Worksheets(2).Range("I14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 4)
' kopier 1-4 farver 5 design
bk.Worksheets(2).Range("I15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 4)
bk.Worksheets(2).Range("I16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 4)
bk.Worksheets(2).Range("I17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 4)
bk.Worksheets(2).Range("I18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 4)
bk.Worksheets(2).Range("I19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 4)
bk.Worksheets(2).Range("I20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 4)
bk.Worksheets(2).Range("I21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 4)
bk.Worksheets(2).Range("I22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 4)
bk.Worksheets(2).Range("I23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 4)
' 1-4, 5 design hertil
bk.Worksheets(2).Range("J14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 5)
' kopier 1-4 farver 6 design
bk.Worksheets(2).Range("J15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 5)
bk.Worksheets(2).Range("J16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 5)
bk.Worksheets(2).Range("J17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 5)
bk.Worksheets(2).Range("J18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 5)
bk.Worksheets(2).Range("J19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 5)
bk.Worksheets(2).Range("J20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 5)
bk.Worksheets(2).Range("J21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 5)
bk.Worksheets(2).Range("J22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 5)
bk.Worksheets(2).Range("J23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 5)
' 1-4, 6 design hertil
bk.Worksheets(2).Range("K14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 6)
' kopier 1-4 farver 7 design
bk.Worksheets(2).Range("K15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 6)
bk.Worksheets(2).Range("K16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 6)
bk.Worksheets(2).Range("K17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 6)
bk.Worksheets(2).Range("K18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 6)
bk.Worksheets(2).Range("K19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 6)
bk.Worksheets(2).Range("K20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 6)
bk.Worksheets(2).Range("K21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 6)
bk.Worksheets(2).Range("K22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 6)
bk.Worksheets(2).Range("K23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 6)
' 1-4, 7 design hertil
bk.Worksheets(2).Range("L14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 7)
' kopier 1-4 farver 8 design
bk.Worksheets(2).Range("L15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 7)
bk.Worksheets(2).Range("L16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 7)
bk.Worksheets(2).Range("L17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 7)
bk.Worksheets(2).Range("L18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 7)
bk.Worksheets(2).Range("L19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 7)
bk.Worksheets(2).Range("L20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 7)
bk.Worksheets(2).Range("L21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 7)
bk.Worksheets(2).Range("L22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 7)
bk.Worksheets(2).Range("L23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 7)
' 1-4, 8 design hertil
' Range("A1:P50").Select ' marker udskrift
' Selection.PrintOut Copies:=1, Collate:=True ' Udskriver
Worksheets(2).Range("B20").Value = "6" ' kopier 6 farver til antal farver
Worksheets(2).Range("N47").Value = Worksheets(2).Range("R2").Value ' sætter
pris pr designskift til 6 frv pris
bk.Worksheets(3).Range("D4").Value = _
Worksheets(2).Range("C2").Value ' kopier kunde
bk.Worksheets(3).Range("D5").Value = _
Worksheets(2).Range("I2").Value ' kopier opgave
bk.Worksheets(3).Range("D7").Value = _
Worksheets(2).Range("B11").Value & " MM " ' kopier bredde
bk.Worksheets(3).Range("D8").Value = _
Worksheets(2).Range("B14").Value ' kopier afsnit
bk.Worksheets(3).Range("C11").Value = "PRIS / " & _
Worksheets(2).Range("B8").Value ' kopier salgsenhed
bk.Worksheets(3).Range("D26").Value = _
Worksheets(2).Range("B38").Value ' kopier Valuta
bk.Worksheets(3).Range("D6").Value = _
Format(Range("B47").Value) & " / " & Format(Range("B48").Value) ' kopier
folietype
bk.Worksheets(3).Range("D9").Value = _
Worksheets(2).Range("B23").Value & "%" ' kopier farvedækning
bk.Worksheets(3).Range("C12").Value = _
Worksheets(2).Range("E4").Value ' kopier Antal herfra og ned
bk.Worksheets(3).Range("C13").Value = _
Worksheets(2).Range("F4").Value
bk.Worksheets(3).Range("C14").Value = _
Worksheets(2).Range("G4").Value
bk.Worksheets(3).Range("C15").Value = _
Worksheets(2).Range("H4").Value
bk.Worksheets(3).Range("C16").Value = _
Worksheets(2).Range("I4").Value
bk.Worksheets(3).Range("C17").Value = _
Worksheets(2).Range("J4").Value
bk.Worksheets(3).Range("C18").Value = _
Worksheets(2).Range("K4").Value
bk.Worksheets(3).Range("C19").Value = _
Worksheets(2).Range("L4").Value
bk.Worksheets(3).Range("C20").Value = _
Worksheets(2).Range("M4").Value
bk.Worksheets(3).Range("C21").Value = _
Worksheets(2).Range("N4").Value
bk.Worksheets(3).Range("C22").Value = _
Worksheets(2).Range("O4").Value
bk.Worksheets(3).Range("C23").Value = _
Worksheets(2).Range("P4").Value ' Antal hertil
bk.Worksheets(3).Range("E12").Value = _
Worksheets(2).Range("E38").Value ' kopier 1 design 5-6 farver
bk.Worksheets(3).Range("E13").Value = _
Worksheets(2).Range("F38").Value
bk.Worksheets(3).Range("E14").Value = _
Worksheets(2).Range("G38").Value
bk.Worksheets(3).Range("E15").Value = _
Worksheets(2).Range("H38").Value
bk.Worksheets(3).Range("E16").Value = _
Worksheets(2).Range("I38").Value
bk.Worksheets(3).Range("E17").Value = _
Worksheets(2).Range("J38").Value
bk.Worksheets(3).Range("E18").Value = _
Worksheets(2).Range("K38").Value
bk.Worksheets(3).Range("E19").Value = _
Worksheets(2).Range("L38").Value
bk.Worksheets(3).Range("E20").Value = _
Worksheets(2).Range("M38").Value
bk.Worksheets(3).Range("E21").Value = _
Worksheets(2).Range("N38").Value
bk.Worksheets(3).Range("E22").Value = _
Worksheets(2).Range("O38").Value
bk.Worksheets(3).Range("E23").Value = _
Worksheets(2).Range("P38").Value ' 5-6 farver hertil
bk.Worksheets(3).Range("F13").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value '
kopier 5-6 farver 2 design
bk.Worksheets(3).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value
bk.Worksheets(3).Range("F15").Value = _
Worksheets(2).Range("H38").Value + Worksheets(2).Range("H41").Value
bk.Worksheets(3).Range("F16").Value = _
Worksheets(2).Range("I38").Value + Worksheets(2).Range("I41").Value
bk.Worksheets(3).Range("F17").Value = _
Worksheets(2).Range("J38").Value + Worksheets(2).Range("J41").Value
bk.Worksheets(3).Range("F18").Value = _
Worksheets(2).Range("K38").Value + Worksheets(2).Range("K41").Value
bk.Worksheets(3).Range("F19").Value = _
Worksheets(2).Range("L38").Value + Worksheets(2).Range("L41").Value
bk.Worksheets(3).Range("F20").Value = _
Worksheets(2).Range("M38").Value + Worksheets(2).Range("M41").Value
bk.Worksheets(3).Range("F21").Value = _
Worksheets(2).Range("N38").Value + Worksheets(2).Range("N41").Value
bk.Worksheets(3).Range("F22").Value = _
Worksheets(2).Range("O38").Value + Worksheets(2).Range("O41").Value
bk.Worksheets(3).Range("F23").Value = _
Worksheets(2).Range("P38").Value + Worksheets(2).Range("P41").Value ' 5-6
2 design hertil
bk.Worksheets(3).Range("G14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 2)
' kopier 5-6 farver 3 design
bk.Worksheets(3).Range("G15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 2)
bk.Worksheets(3).Range("G16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 2)
bk.Worksheets(3).Range("G17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 2)
bk.Worksheets(3).Range("G18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 2)
bk.Worksheets(3).Range("G19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 2)
bk.Worksheets(3).Range("G20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 2)
bk.Worksheets(3).Range("G21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 2)
bk.Worksheets(3).Range("G22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 2)
bk.Worksheets(3).Range("G23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 2)
' 5-6 3 design hertil
bk.Worksheets(3).Range("H14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 3)
' kopier 5-6 farver 4 design
bk.Worksheets(3).Range("H15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 3)
bk.Worksheets(3).Range("H16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 3)
bk.Worksheets(3).Range("H17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 3)
bk.Worksheets(3).Range("H18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 3)
bk.Worksheets(3).Range("H19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 3)
bk.Worksheets(3).Range("H20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 3)
bk.Worksheets(3).Range("H21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 3)
bk.Worksheets(3).Range("H22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 3)
bk.Worksheets(3).Range("H23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 3)
' 5-6 4 design hertil
bk.Worksheets(3).Range("I14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 4)
' kopier 5-6 farver 5 design
bk.Worksheets(3).Range("I15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 4)
bk.Worksheets(3).Range("I16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 4)
bk.Worksheets(3).Range("I17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 4)
bk.Worksheets(3).Range("I18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 4)
bk.Worksheets(3).Range("I19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 4)
bk.Worksheets(3).Range("I20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 4)
bk.Worksheets(3).Range("I21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 4)
bk.Worksheets(3).Range("I22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 4)
bk.Worksheets(3).Range("I23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 4)
' 5-6, 5 design hertil
bk.Worksheets(3).Range("J14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 5)
' kopier 5-6 farver 6 design
bk.Worksheets(3).Range("J15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 5)
bk.Worksheets(3).Range("J16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 5)
bk.Worksheets(3).Range("J17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 5)
bk.Worksheets(3).Range("J18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 5)
bk.Worksheets(3).Range("J19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 5)
bk.Worksheets(3).Range("J20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 5)
bk.Worksheets(3).Range("J21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 5)
bk.Worksheets(3).Range("J22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 5)
bk.Worksheets(3).Range("J23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 5)
' 5-6, 6 design hertil
bk.Worksheets(3).Range("K14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 6)
' kopier 5-6 farver 7 design
bk.Worksheets(3).Range("K15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 6)
bk.Worksheets(3).Range("K16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 6)
bk.Worksheets(3).Range("K17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 6)
bk.Worksheets(3).Range("K18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 6)
bk.Worksheets(3).Range("K19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 6)
bk.Worksheets(3).Range("K20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 6)
bk.Worksheets(3).Range("K21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 6)
bk.Worksheets(3).Range("K22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 6)
bk.Worksheets(3).Range("K23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 6)
' 5-6, 7 design hertil
bk.Worksheets(3).Range("L14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 7)
' kopier 5-6 farver 8 design
bk.Worksheets(3).Range("L15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 7)
bk.Worksheets(3).Range("L16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 7)
bk.Worksheets(3).Range("L17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 7)
bk.Worksheets(3).Range("L18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 7)
bk.Worksheets(3).Range("L19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 7)
bk.Worksheets(3).Range("L20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 7)
bk.Worksheets(3).Range("L21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 7)
bk.Worksheets(3).Range("L22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 7)
bk.Worksheets(3).Range("L23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 7)
' 5-6, 8 design hertil
' Range("A1:P50").Select ' marker udskrift
' Selection.PrintOut Copies:=1, Collate:=True ' Udskriver
Worksheets(2).Range("B20").Value = "8" ' kopier 8 farver til antal farver
Worksheets(2).Range("N47").Value = Worksheets(2).Range("R3").Value ' sætter
pris pr designskift til 8 frv pris
bk.Worksheets(4).Range("D4").Value = _
Worksheets(2).Range("C2").Value ' kopier kunde
bk.Worksheets(4).Range("D5").Value = _
Worksheets(2).Range("I2").Value ' kopier opgave
bk.Worksheets(4).Range("D7").Value = _
Worksheets(2).Range("B11").Value & " MM " ' kopier bredde
bk.Worksheets(4).Range("D8").Value = _
Worksheets(2).Range("B14").Value ' kopier afsnit
bk.Worksheets(4).Range("C11").Value = "PRIS / " & _
Worksheets(2).Range("B8").Value ' kopier salgsenhed
bk.Worksheets(4).Range("D26").Value = _
Worksheets(2).Range("B38").Value ' kopier Valuta
bk.Worksheets(4).Range("D6").Value = _
Format(Range("B47").Value) & " / " & Format(Range("B48").Value) ' kopier
folietype
bk.Worksheets(4).Range("D9").Value = _
Worksheets(2).Range("B23").Value & "%" ' kopier farvedækning
bk.Worksheets(4).Range("C12").Value = _
Worksheets(2).Range("E4").Value ' kopier Antal herfra og ned
bk.Worksheets(4).Range("C13").Value = _
Worksheets(2).Range("F4").Value
bk.Worksheets(4).Range("C14").Value = _
Worksheets(2).Range("G4").Value
bk.Worksheets(4).Range("C15").Value = _
Worksheets(2).Range("H4").Value
bk.Worksheets(4).Range("C16").Value = _
Worksheets(2).Range("I4").Value
bk.Worksheets(4).Range("C17").Value = _
Worksheets(2).Range("J4").Value
bk.Worksheets(4).Range("C18").Value = _
Worksheets(2).Range("K4").Value
bk.Worksheets(4).Range("C19").Value = _
Worksheets(2).Range("L4").Value
bk.Worksheets(4).Range("C20").Value = _
Worksheets(2).Range("M4").Value
bk.Worksheets(4).Range("C21").Value = _
Worksheets(2).Range("N4").Value
bk.Worksheets(4).Range("C22").Value = _
Worksheets(2).Range("O4").Value
bk.Worksheets(4).Range("C23").Value = _
Worksheets(2).Range("P4").Value ' Antal hertil
bk.Worksheets(4).Range("E12").Value = _
Worksheets(2).Range("E38").Value ' kopier 1 design 7-8 farver
bk.Worksheets(4).Range("E13").Value = _
Worksheets(2).Range("F38").Value
bk.Worksheets(4).Range("E14").Value = _
Worksheets(2).Range("G38").Value
bk.Worksheets(4).Range("E15").Value = _
Worksheets(2).Range("H38").Value
bk.Worksheets(4).Range("E16").Value = _
Worksheets(2).Range("I38").Value
bk.Worksheets(4).Range("E17").Value = _
Worksheets(2).Range("J38").Value
bk.Worksheets(4).Range("E18").Value = _
Worksheets(2).Range("K38").Value
bk.Worksheets(4).Range("E19").Value = _
Worksheets(2).Range("L38").Value
bk.Worksheets(4).Range("E20").Value = _
Worksheets(2).Range("M38").Value
bk.Worksheets(4).Range("E21").Value = _
Worksheets(2).Range("N38").Value
bk.Worksheets(4).Range("E22").Value = _
Worksheets(2).Range("O38").Value
bk.Worksheets(4).Range("E23").Value = _
Worksheets(2).Range("P38").Value ' 7-8 farver hertil
bk.Worksheets(4).Range("F13").Value = _
Worksheets(2).Range("F38").Value + Worksheets(2).Range("F41").Value '
kopier 7-8 farver 2 design
bk.Worksheets(4).Range("F14").Value = _
Worksheets(2).Range("G38").Value + Worksheets(2).Range("G41").Value
bk.Worksheets(4).Range("F15").Value = _
Worksheets(2).Range("H38").Value + Worksheets(2).Range("H41").Value
bk.Worksheets(4).Range("F16").Value = _
Worksheets(2).Range("I38").Value + Worksheets(2).Range("I41").Value
bk.Worksheets(4).Range("F17").Value = _
Worksheets(2).Range("J38").Value + Worksheets(2).Range("J41").Value
bk.Worksheets(4).Range("F18").Value = _
Worksheets(2).Range("K38").Value + Worksheets(2).Range("K41").Value
bk.Worksheets(4).Range("F19").Value = _
Worksheets(2).Range("L38").Value + Worksheets(2).Range("L41").Value
bk.Worksheets(4).Range("F20").Value = _
Worksheets(2).Range("M38").Value + Worksheets(2).Range("M41").Value
bk.Worksheets(4).Range("F21").Value = _
Worksheets(2).Range("N38").Value + Worksheets(2).Range("N41").Value
bk.Worksheets(4).Range("F22").Value = _
Worksheets(2).Range("O38").Value + Worksheets(2).Range("O41").Value
bk.Worksheets(4).Range("F23").Value = _
Worksheets(2).Range("P38").Value + Worksheets(2).Range("P41").Value ' 7-8
2 design hertil
bk.Worksheets(4).Range("G14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 2)
' kopier 7-8 farver 3 design
bk.Worksheets(4).Range("G15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 2)
bk.Worksheets(4).Range("G16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 2)
bk.Worksheets(4).Range("G17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 2)
bk.Worksheets(4).Range("G18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 2)
bk.Worksheets(4).Range("G19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 2)
bk.Worksheets(4).Range("G20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 2)
bk.Worksheets(4).Range("G21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 2)
bk.Worksheets(4).Range("G22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 2)
bk.Worksheets(4).Range("G23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 2)
' 7-8 3 design hertil
bk.Worksheets(4).Range("H14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 3)
' kopier 7-8 farver 4 design
bk.Worksheets(4).Range("H15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 3)
bk.Worksheets(4).Range("H16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 3)
bk.Worksheets(4).Range("H17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 3)
bk.Worksheets(4).Range("H18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 3)
bk.Worksheets(4).Range("H19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 3)
bk.Worksheets(4).Range("H20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 3)
bk.Worksheets(4).Range("H21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 3)
bk.Worksheets(4).Range("H22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 3)
bk.Worksheets(4).Range("H23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 3)
' 7-8 4 design hertil
bk.Worksheets(4).Range("I14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 4)
' kopier 7-8 farver 5 design
bk.Worksheets(4).Range("I15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 4)
bk.Worksheets(4).Range("I16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 4)
bk.Worksheets(4).Range("I17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 4)
bk.Worksheets(4).Range("I18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 4)
bk.Worksheets(4).Range("I19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 4)
bk.Worksheets(4).Range("I20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 4)
bk.Worksheets(4).Range("I21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 4)
bk.Worksheets(4).Range("I22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 4)
bk.Worksheets(4).Range("I23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 4)
' 7-8, 5 design hertil
bk.Worksheets(4).Range("J14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 5)
' kopier 7-8 farver 6 design
bk.Worksheets(4).Range("J15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 5)
bk.Worksheets(4).Range("J16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 5)
bk.Worksheets(4).Range("J17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 5)
bk.Worksheets(4).Range("J18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 5)
bk.Worksheets(4).Range("J19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 5)
bk.Worksheets(4).Range("J20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 5)
bk.Worksheets(4).Range("J21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 5)
bk.Worksheets(4).Range("J22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 5)
bk.Worksheets(4).Range("J23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 5)
' 7-8, 6 design hertil
bk.Worksheets(4).Range("K14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 6)
' kopier 7-8 farver 7 design
bk.Worksheets(4).Range("K15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 6)
bk.Worksheets(4).Range("K16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 6)
bk.Worksheets(4).Range("K17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 6)
bk.Worksheets(4).Range("K18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 6)
bk.Worksheets(4).Range("K19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 6)
bk.Worksheets(4).Range("K20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 6)
bk.Worksheets(4).Range("K21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 6)
bk.Worksheets(4).Range("K22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 6)
bk.Worksheets(4).Range("K23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 6)
' 7-8, 7 design hertil
bk.Worksheets(4).Range("L14").Value = _
Worksheets(2).Range("G38").Value + (Worksheets(2).Range("G41").Value * 7)
' kopier 7-8 farver 8 design
bk.Worksheets(4).Range("L15").Value = _
Worksheets(2).Range("H38").Value + (Worksheets(2).Range("H41").Value * 7)
bk.Worksheets(4).Range("L16").Value = _
Worksheets(2).Range("I38").Value + (Worksheets(2).Range("I41").Value * 7)
bk.Worksheets(4).Range("L17").Value = _
Worksheets(2).Range("J38").Value + (Worksheets(2).Range("J41").Value * 7)
bk.Worksheets(4).Range("L18").Value = _
Worksheets(2).Range("K38").Value + (Worksheets(2).Range("K41").Value * 7)
bk.Worksheets(4).Range("L19").Value = _
Worksheets(2).Range("L38").Value + (Worksheets(2).Range("L41").Value * 7)
bk.Worksheets(4).Range("L20").Value = _
Worksheets(2).Range("M38").Value + (Worksheets(2).Range("M41").Value * 7)
bk.Worksheets(4).Range("L21").Value = _
Worksheets(2).Range("N38").Value + (Worksheets(2).Range("N41").Value * 7)
bk.Worksheets(4).Range("L22").Value = _
Worksheets(2).Range("O38").Value + (Worksheets(2).Range("O41").Value * 7)
bk.Worksheets(4).Range("L23").Value = _
Worksheets(2).Range("P38").Value + (Worksheets(2).Range("P41").Value * 7)
' 7-8, 8 design hertil

' Range("A1:P50").Select ' marker udskrift YES
' Selection.PrintOut Copies:=1, Collate:=True ' Udskriver YES
MsgBox "Prisliste er genereret og der er skrevet 3 stk. forkalkyler ud" YES
'bk.Save YES
'If bClose Then YES
'bk.Close Savechanges:=False YES
'End If YES
Application.EnableEvents = False YES
'ThisWorkbook.Save YES
Application.EnableEvents = True YES
Unload Me ' lukker vælg prisliste YES
End Sub YES

Please help
Ole

"Bob Phillips" skrev i en meddelelse
...

Dim sFile As String
Dim oWb As Workbook

sFile = "c:\mytest\myfile..xls"
On Error Resume Next
Set oWb = Workbooks.Open (sFile)
On Errtor Goto 0
If oWb Is Nothing Then
MsgBox "File not found"
Else
ThisWorkbook.Worksheets("Sheet1").Range("A1:H100") .Copy _
oWb.Worksheets("Sheet1").Range("A1")
End If

You could also use GetOpenFileName to use a dialog box to navigate to the
file and open that

Dim sFile As String
Dim oWb As Workbook

sFile = "c:\mytest\myfile..xls"
On Error Resume Next
File = Application.GetOpenFilename( _
"Microsft Excel Files (*.xls), *.xls")
If sFile < False Then
Set oWb = Workbooks.Open(sFile)
On Errtor GoTo 0
If oWb Is Nothing Then
MsgBox "File not found"
Else
ThisWorkbook.Worksheets("Sheet1").Range("A1:H100") .Copy _
oWb.Worksheets("Sheet1").Range("A1")
End If
End If

And it is appreciated :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ole" wrote in message
...
Hi,

I want to copy som cells from workbook 1 to a closed workbook 2 with a
commanbutton,
when i click the command button from workbook1 i want i to open
workbook2
and copy the
cells to workbook2, anyone got an idea.

Any help is apressiaded ( and that i cant spell to :-)

Ole


--
Jeg beskyttes af den gratis SPAMfighter til privatbrugere.
Den har indtil videre sparet mig for at få 0 spam-mails.
Betalende brugere får ikke denne besked i deres e-mails.
Hent den gratis her: www.spamfighter.dk






Reply With QuoteReply With Quote