such next free rows

[Louis]

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_validierung.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]

Try replacing:
Range("A4").Select
with:
Cells(rows.Count,1).end(xlup).offset(1).select

 

[OssieMac]

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_validierung.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 '

 

[Louis]

Thanks to both of you it works

Brgds

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_validierung.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 '