Excel - Copy from one worksheet to another

Asked By JayDe

21-Nov-09 05:36 AM

I have some code that calculates how many lines I need to copy from one
worksheet to another, but when I do the copy I get an errormessage.

Here is part of my code:


Private Sub Start_Click()

Dim lastline As Integer
Dim MalWb, RappWb As Workbook


Workbooks.OpenText Filename:=RapportFrm.Innfil.Value, Origin:=xlWindows,
StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote,
ConsecutiveDelimiter _
Other:=True, OtherChar:="""", FieldInfo:=Array(Array(1, 9), Array(2,
1), _
Array(3, 9), Array(4, 1), Array(5, 9), Array(6, 1), Array(7, 9),
Array(8, 1), Array(9, 9), _
Array(10, 1), Array(11, 9), Array(12, 1), Array(13, 9), Array(14,
1), Array(15, 9)), _
DecimalSeparator:=".", TrailingMinusNumbers:=False

Worksheets(1).Name = "Rapport"

' Some code to calculate lastline


Set RappWb = Workbooks(ActiveWorkbook.Name)

Workbooks.Open RapportFrm.MalFil.Value

Set MalWb = Workbooks(ActiveWorkbook.Name)

'Not working
RappWb.Worksheets("Rapport").Range(Cells(2, 1), Cells(lastline - 1, 8)).Copy
MalWb.Worksheets("Resultat").Cells(7, 1)


End Sub

21-Nov-09 09:10 AM

I do not see any code to actually calculate the value for variable 'lastline'.

I am not sure what column you want to use to get that from, but here is a line
of code that should help:
'existing line of code
Set RappWb = Workbooks(ActiveWorkbook.Name)
'add this right after that line
lastline = RappWb.Worksheets("Rapport").Range("A" & Rows.Count).End(xlUp).Row

that will set lastline to the last row in column A that has a value or
formula in it.  Change "A" to whatever column is most appropriate for your
sheet.