Selected row--->other workbook?

Quote:

---

Originally Posted by erin64

The zip here, the first one was too large.
And by the way Jonske, it's she:hi:

---

:oops: sorry...

No, the attachment's not there, after you post it, have a look, (or maybe you can see it in "Preview Post") to see if you have a symbol there and some writing saying 'something or other' .zip

Hi John,

yes you right, I goofed. Well lets try with the file when she posts it...

By the way erin,

I've had problems uploading attachments before, dont know the reason why, but at some times of the day it just cant be done yet if you try a few hours later it does it straight away :dunno

Oh well, it's well past :sleeping: :snooze :snore: time here in OZ :hi: ...later

By my calculations its about midnight there. Do you sleep so early?

Hi erin,

what am I expected to answer here?

Quote:

---

Originally Posted by erin64

Kirjoita nimi?

---

It's search name, but it searches anything:)
:whistle: :rotlaugh:

Hi, I was wondering where it figures out what is the other workbook. The target is Veroinsiirto and the source Ast.? Stubid ? again...

Code:

---

Option Explicit
 
 Sub CopyAndPrint()
Dim Wkb            As Workbook
    Dim WS              As Worksheet
    Dim TotalRows      As Long
    Dim i              As Long
    Dim StartRow        As Long
Application.ScreenUpdating = False
TotalRows = Selection.Rows.Count
    StartRow = Selection(1, 1).Row
    Set Wkb = Workbooks("Veroinsiirto.xls")
    Set WS = Wkb.Sheets("Taul2")
For i = StartRow To StartRow + TotalRows - 1
        WS.Range("BB2").Value = _
        ThisWorkbook.Sheets("Temp").Range("A2").Value
WS.Range("BB2").Value = _
        ThisWorkbook.Sheets("Temp").Range("J2").Value
WS.Range("A2").Value = _
        ThisWorkbook.Sheets("Temp").Range("A6").Value
WS.Range("B2").Value = _
        ThisWorkbook.Sheets("Temp").Range("J6").Value
WS.Range("AO2").Value = _
        ThisWorkbook.Sheets("Temp").Range("H8").Value
WS.Range("Z2").Value = _
        ThisWorkbook.Sheets("Temp").Range("A16").Value
WS.Range("AZ2").Value = _
        ThisWorkbook.Sheets("Temp").Range("J16").Value
WS.Range("AA2").Value = _
        ThisWorkbook.Sheets("Temp").Range("M16").Value
WS.Range("BD2").Value = _
        ThisWorkbook.Sheets("Temp").Range("A23").Value
WS.Range("BE2").Value = _
        ThisWorkbook.Sheets("Temp").Range("J23").Value
WS.Range("BF2").Value = _
        ThisWorkbook.Sheets("Temp").Range("M23").Value
WS.Range("AV2").Value = _
        ThisWorkbook.Sheets("Temp").Range("D36").Value
WS.Range("AO2").Value = _
        ThisWorkbook.Sheets("Temp").Range("D10").Value
        WS.PrintOut Copies:=6
    Next i
Wkb.Close SaveChanges:=False
Application.ScreenUpdating = True
Set WS = Nothing
    Set Wkb = Nothing
End Sub

---

WS is the sheet you want to fill out in Veroinsiirto.xls.
ThisWorkbook.Sheets("Temp") is the sheet you want to get the data from.

For reference only, see also:

http://www.ozgrid.com/forum/showthre...662#post148662

Quote:

---

Originally Posted by erin64

Hi, I was wondering where it figures out what is the other workbook. The target is Veroinsiirto and the source Ast.? Stubid ? again...

---

Code:

---

Set Wkb = Workbooks("Veroinsiirto.xls")

---

Wkb is now set to the other Workbook.

Thanks for help, actually I figured with somebody out that the sheet1in Veroinsiirto was named Taul2, changed that, starts printing the right sheet, but no data added and prints thousands of thousands copies...
Gives an error report Run-time error 1004, Method Print out of object..worksheet failed?:banghead: :banghead:

For a start, move this line after
Next i

Code:

---

WS.PrintOut Copies:=6

---

Code:

---

WS.Range("A2").Value = _       
ThisWorkbook.Sheets("Temp").Range("A6").Value

---

This is taking the data from Temp. In your attachment Temp has no data.

Next, you have TWO things that are being put into BB2 and AO2, the second of each would over-write the first. There may be more errors as well, but I'm not going to look at the lot right now....

Hi U all, you are so dears..

First of all I changed the printing line, and AO2 is needed in two different places in the target sheet...Problem?

And temp will have information when You run the other code and find customers info and change it...

Next?:rofl

It's two o'clock here and I've been waken since 6 this morning... Been gazing this code most of the time, will go to school 8 o'clock:wot :think: :whip

You can put AO2 in as many places as you want. But I believe you have it backwards in the code.

Range(Range Where Data Is Going) = Range(Range Where Data Is From)

Code:

---

WS.Range("AO2").Value = _
          ThisWorkbook.Sheets("Temp").Range("D10").Value

---

This means change the value in WS.Range("AO2") so that it is equal to the value in Sheets("Temp").Range("D10")

Do you mean to do this?

Code:

---

WS.Range("D10").Value = _
          ThisWorkbook.Sheets("Temp").Range("AO2").Value

---

Yes, from ao2-d10, going to change that but it doesn't solve the problem does it?

Changed and now run time error 1004, application defined or object defined???

It looks like this nowadays:wot

Code:

---

Sub CopyAndPrint()
Dim Wkb            As Workbook
Dim WS              As Worksheet
Dim TotalRows      As Long
Dim i              As Long
Dim StartRow        As Long
Application.ScreenUpdating = False
TotalRows = Selection.Rows.Count
StartRow = Selection(1, 1).Row
Application.Workbooks.Add ("Veroinsiirto.xls")
Set Wkb = Workbooks("Veroinsiirto.xls")
Set WS = Wkb.Sheets("Temp")
For i = StartRow To StartRow + TotalRows - 1
WS.Range("A2").Value = _
ThisWorkbook.Sheets("Temp").Range("BB2").Value
WS.Range("J2").Value = _
ThisWorkbook.Sheets("Temp").Range("BB2").Value
WS.Range("BB2").Value = _
ThisWorkbook.Sheets("Temp").Range("A2").Value
WS.Range("J6").Value = _
ThisWorkbook.Sheets("Temp").Range("B2").Value
WS.Range("H8").Value = _
ThisWorkbook.Sheets("Temp").Range("AO2").Value
WS.Range("A16").Value = _
ThisWorkbook.Sheets("Temp").Range("Z2").Value
WS.Range("J16").Value = _
ThisWorkbook.Sheets("Temp").Range("AZ2").Value
WS.Range("").Value = _
ThisWorkbook.Sheets("Temp").Range("AA2").Value
WS.Range("A23").Value = _
ThisWorkbook.Sheets("Temp").Range("BD2").Value
WS.Range("").Value = _
ThisWorkbook.Sheets("Temp").Range("BE2").Value
WS.Range("M23").Value = _
ThisWorkbook.Sheets("Temp").Range("BF2").Value
WS.Range("D36").Value = _
ThisWorkbook.Sheets("Temp").Range("AV2").Value
WS.Range("D10").Value = _
ThisWorkbook.Sheets("Temp").Range("AO2").Value
Next i
WS.PrintOut Copies:=6
Wkb.Close SaveChanges:=False
Application.ScreenUpdating = True
Set WS = Nothing
Set Wkb = Nothing
Application.Windows("Veroinsiirto.xls").Close
End Sub

---

This might be easier to read and sort out what needs to go where, and, reading back thru the earier posts I see you do want printout inside the loop. Try putting some data into temp:

Code:

---

Sub CopyAndPrint()
Dim Wkb As Workbook
Dim WS As Worksheet
Dim ThisBook
Dim i As Long
Set Wkb = Workbooks("Veroinsiirto.xls")
Set WS = Wkb.Sheets("Taul2")
Set ThisBook = ThisWorkbook.Sheets("Temp")
Application.ScreenUpdating = False
For i = Selection(1, 1).Row To Selection(1, 1). _
Row + Selection.Rows.Count - 1
'Going TO HERE    <<<<<<    FROM HERE
WS.Range("AO2") = ThisBook.Range("H8")
WS.Range("AO2") = ThisBook.Range("D10")
WS.Range("BB2") = ThisBook.Range("A2")
WS.Range("BB2") = ThisBook.Range("J2")
WS.Range("A2") = ThisBook.Range("A6")
WS.Range("B2") = ThisBook.Range("J6")
WS.Range("Z2") = ThisBook.Range("A16")
WS.Range("AZ2") = ThisBook.Range("J16")
WS.Range("AA2") = ThisBook.Range("M16")
WS.Range("BD2") = ThisBook.Range("A23")
WS.Range("BE2") = ThisBook.Range("J23")
WS.Range("BF2") = ThisBook.Range("M23")
WS.Range("AV2") = ThisBook.Range("D36")
WS.PrintOut Copies:=6
Next i
Wkb.Close SaveChanges:=False
Set WS = Nothing
Set Wkb = Nothing
Set ThisBook = Nothing
End Sub

---

Hi there,

Now it prints allright, but no data added....


Is this always like this, after one problem solved, next jumps in???:think: :doh: :banghead: