Selected row--->other workbook?

[erin64]

Hi U there!

Probably a stupid question and a simple answer, but for me it's all new... I tried to look but.....

I know there is some kind of way to link one cell from workbook1 to workbook 2.
But how it is done? The cells don't have the same position in both workbooks and I don't need to copy all the information.

Is there a way after linking these too togeher, to make a command button and put some kind of a macro attached to it? So that everytime one selects a row the information copies into woorkbook and then prints it..? Dont have to save the document but I do need 6 prints of the workbook2? Then selecting another row in workbook1 and doing the same all over again...

If something like this exists, it would help my work tremendously... Now I am adding the information manually by copying and pasting and printing...
You can imagine I do this about to 80-100 rows a day, 6 copies of each, about 20 cells to link....

So any help is more than wellcomed,

-erin-

[Jacob Hilderbrand]

Try this to get you started. It will copy the active row to a new workbook. If you want to do this for a certain number of rows automatically we can do that too (i.e., copy rows 1-100 seperately to a new workbook and print 6 copies each).

Option Explicit

Sub CopyAndPrint()
Dim Wkb             As Workbook
Application.ScreenUpdating = False
ActiveCell.EntireRow.Copy
    Set Wkb = Workbooks.Add
    ActiveSheet.Paste
    Wkb.Sheets("Sheet1").PrintOut Copies:=6
    Wkb.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

[erin64]

Thanks for the quick answer, I knew I could count on You all...


But couple of ?s before I even try...

How does it knov which excel workbooks and sheets are in question? And how does it know that in

Asiakkaat.xls|sheet1|A1 goes to Veroinsiirto.xls|Sheet1|B2

and next piece of needed information is

Asiakkaat.xls|sheet1|D1 goes to Veroinsiirto.xls|Sheet1|G10


Any help?


-erin- embarassed

[Jacob Hilderbrand]

We can specify the workbooks and sheets, but you need to let me know a bit more so I know where the data is going.

So you have a cell in Row 1 selected and run the macro.
A1 is copied to B2 in a second workbook.
D1 is copied to G10 in a second workbook.

Why is the data going there? Does it always go to the same place in the workbook and overwrite the old data or does it actually go to the next available row?

[erin64]

Hi again, You are a life saver

The sheet 2 has a form that is reguired by authoroties in 6 copies. The data always goes to same places and it doesn't have to save it....
It would help to be able to select multiple rows and then let the machine do the rest...

We are collecting customer data and every now and then (tens of times a day) move all the changed data to sheet 1 which is also cleared after moving the data to the forms and copied *6.

Any clearer? Sorry my bad english... and uncleariness

thanks -erin-

[erin64]

So I open both workbooks, select a row1. Then start recording the macro? Select A1,ctrl C, go to workbook2, select B2,ctrl V,then click go back to workbook1 and do the same D1 and the rest of stuff?

And with that piece of code it knows where to but A333??? Wauuu!

[Jacob Hilderbrand]

Put this code in Asiakkaat.xls and make sure both workbooks are opened.

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("Sheet1")
For i = StartRow To StartRow + TotalRows - 1
        WS.Range("B2").Value = _
        ThisWorkbook.Sheets("Sheet1").Range("A1").Value
WS.Range("G10").Value = _
        ThisWorkbook.Sheets("Sheet1").Range("D1").Value
        WS.PrintOut Copies:=6
    Next i
Wkb.Close SaveChanges:=False
Application.ScreenUpdating = True
Set WS = Nothing
    Set Wkb = Nothing
End Sub

[erin64]

Hi there, it's me bothering again

I wrote it. Put the cells and changed the names and so on. I copied the code into into Sheet Temp (actually sheet2, but named later as Temp.) I try to run the code with F8, it stops and says: Runtime error '9': subscript out of range..
Whatever that means...
I probably understood something wrong, because it doesn't work. Any idea?
I'll enclose the code here:

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("Temp")
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

[johnske]

Hi Erin,

'subscript out of range' generally means that the sheet or book that you're referencing either doesn't exist or, if it's a workbook, isn't open.

Make sure you have named the sheets and book EXACTLY as they appear in the code, any simple spelling mistake such as an extra space between two words or an added full-stop will cause that error message. Also, are both books open when you run the code? If not, that'll also generate the error message.

Regards,
John

[johnske]

PS: With error messages it always help to click the "Debug" button and have a look at exactly what line of code is highlighted and let us know what line that is. (This gives us a clue as to where the error may be..)

[Paleo]

Use:

Application.Workbooks.Add ("Veroinsiirto.xls")
    TotalRows = Selection.Rows.Count 
    StartRow = Selection(1, 1).Row 
    Set Wkb = Workbooks("Veroinsiirto.xls") 
    Set WS = Wkb.Sheets("Temp")

at the beggining of your macro.

[Paleo]

Dont forget the:

Application.Windows("Veroinsiirto.xls").Close

At the end of your macro, just before the "End Sub".

[erin64]

Hi Johnske, You're probably right. I'll check it... Thanks for help.

And I'll be back with more stupid questions... ....

[erin64]

Paleo, Thanks for your help, but I don't have a clue what are You talking about....:=0

[erin64]

Still can't figure what's wrong...

StartRow = Selection(1, 1).Row

(does it matter, that in workbook1, sheet2, the row 1 is Titles???)

I'm turning grey overnight here, but am very happy that You have helped me so much

[johnske]

Still can't figure what's wrong...

StartRow = Selection(1, 1).Row (does it matter, that in workbook1, sheet2, the row 1 is Titles???)

I'm turning grey overnight here, but am very happy that You have helped me so much

Hi erin,

Probably the best way to handle this is to zip a copy of what you've got and post it here (no need to include every entry that may have any sensitive data) so one of us can fix it for you and post it back.

(Go to manage attachments when you're posting, select your zip file, then click "upload" to include it in your post )

[Paleo]

Erin,

you must open the file before using it. Try this 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 
    Application.Workbooks.Add ("Veroinsiirto.xls")
    Set Wkb = Workbooks("Veroinsiirto.xls") 
    Set WS = Wkb.Sheets("Temp") 
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 
    Application.Windows("Veroinsiirto.xls").Close
End Sub

[erin64]

Thanks Paleo,
Gives the same error ....

I'll zip the files and send them here... There are other problems too if someone doesn't have anything else to do ... ( Private Sub Etsi_Click() should be prevented to search the temp sheet, or it never stops ), and the same sub saves the temp, shouldn't save the commandbuttons also...) I hope You don't die of laughter here, I just started January...

But this is the main thing and can't go further untill it works...

Anybody wants a beer, I sure could use one....

[johnske]

Paleo, he already has a workbook "Veroinsiirto.xls", using this code you're giving >> Application.Workbooks.Add ("Veroinsiirto.xls") ... is adding a new book. i.e. a copy of Veroinsiirto.xls... If you want to open the existing book you should use something similar to this:

On Error Resume Next  '<< error handling for if the book's already open
Application.Workbooks.Open(ActiveWorkbook.Path & "\Veroinsiirto.xls") 
Workbooks("Veroinsiirto.xls").Activate

John

[erin64]

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