such next free rows
Hello
i need help to finalyse the vba code. the vba code is working but i need to find the first empty line and paste the data from the (excel base.xls). can someone help me Thanks Dim Wk As Workbook Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") Windows("excel base.xls").Activate Range("B1:B75").Select Selection.Copy Windows("Databasere_validierung.xls").Activate Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True 'Wk.Close True' End Sub |
such next free rows
Try replacing: Range("A4").Select with: Cells(rows.Count,1).end(xlup).offset(1).select Louis;703153 Wrote: Hello i need help to finalyse the vba code. the vba code is working but i need to find the first empty line and paste the data from the (excel base.xls). can someone help me Thanks Dim Wk As Workbook Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") Windows("excel base.xls").Activate Range("B1:B75").Select Selection.Copy Windows("Databasere_validierung.xls").Activate Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True 'Wk.Close True' End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=196730 http://www.thecodecage.com/forumz |
such next free rows
Hi Louis,
I have added a bit more to your code. You cannot be sure what worksheet your workbooks will open at. Depends on what sheet it was on when last saved and closed and therefore you need to specify the worksheet. when the worksheet is assigned to the newly opened workbook in the following code, you can just use the variable for the worksheet because VBA knows what workbook it belongs to from when it was assigned. Also it is almost never necessary to select workbooks or worksheets. Just address the ranges involved. Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. Dim Wk As Workbook Dim Ws As Worksheet Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") 'Assign the worksheet to a variable also '(Should always specify the worksheet) Set Ws = Wk.Sheets("Sheet1") 'Include the sheet name With Workbooks("excel base.xls").Sheets("Sheet1") 'No need to select. Just copy range .Range("B1:B75").Copy With Ws .Cells(.Rows.Count, "A") _ .End(xlUp).Offset(1, 0) _ .PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True End With End With Wk.Close True ' -- Regards, OssieMac "Louis" wrote: Hello i need help to finalyse the vba code. the vba code is working but i need to find the first empty line and paste the data from the (excel base.xls). can someone help me Thanks Dim Wk As Workbook Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") Windows("excel base.xls").Activate Range("B1:B75").Select Selection.Copy Windows("Databasere_validierung.xls").Activate Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True 'Wk.Close True' End Sub |
such next free rows
Thanks to both of you it works
Brgds "OssieMac" wrote: Hi Louis, I have added a bit more to your code. You cannot be sure what worksheet your workbooks will open at. Depends on what sheet it was on when last saved and closed and therefore you need to specify the worksheet. when the worksheet is assigned to the newly opened workbook in the following code, you can just use the variable for the worksheet because VBA knows what workbook it belongs to from when it was assigned. Also it is almost never necessary to select workbooks or worksheets. Just address the ranges involved. Note that a space and underscore at the end of a line is a line break in an otherwise single line of code. Dim Wk As Workbook Dim Ws As Worksheet Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") 'Assign the worksheet to a variable also '(Should always specify the worksheet) Set Ws = Wk.Sheets("Sheet1") 'Include the sheet name With Workbooks("excel base.xls").Sheets("Sheet1") 'No need to select. Just copy range .Range("B1:B75").Copy With Ws .Cells(.Rows.Count, "A") _ .End(xlUp).Offset(1, 0) _ .PasteSpecial _ Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, _ Transpose:=True End With End With Wk.Close True ' -- Regards, OssieMac "Louis" wrote: Hello i need help to finalyse the vba code. the vba code is working but i need to find the first empty line and paste the data from the (excel base.xls). can someone help me Thanks Dim Wk As Workbook Set Wk = Workbooks.Open(Filename:="C:\Databasere_validierun g.xls") Windows("excel base.xls").Activate Range("B1:B75").Select Selection.Copy Windows("Databasere_validierung.xls").Activate Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=True 'Wk.Close True' End Sub |
All times are GMT +1. The time now is 09:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com