Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I free up the space occupied by empty rows? | Excel Discussion (Misc queries) | |||
EARN,20000$ & ALL TYPES SOFTWARE FREE DOWNLOAD,& KATRINA KAIF OPENVIDEOS & WEBSITE DEV TO FREE ALL OVER THE WORLD`S PEOPLE | Excel Worksheet Functions | |||
Free social world wide network pays for 10 generations! Free! | Excel Discussion (Misc queries) | |||
Where free 2 sigma statistical process control chart, free? | Charts and Charting in Excel | |||
Free books on Charting, Basics, Data Management, Functions; Free Add-In "Excel Usability Enhancer" | Excel Programming |