CODEWITHSUNDEEP
vbaexcel
Sahil Chaudhary
Sahil Chaudhary

Reputation: 503

Error while copying content from one sheet to another

I am trying to concatenate sheet CurrentDistribution with the sheet distribution1. To do that I look at the last row of distribution1 and copy the content of CurrentDistribution after that last row.

LastCellCurrentDistribution = FindLastCell(CurrentDistribution)
LastCellDistribution1 = FindLastCell("distribution1")
LastRowInDistribution1 = Right(LastCellDistribution1, Len(LastCellDistribution1) - 1)
Sheets(CurrentDistribution).Range("A4:" + LastCellCurrentDistribution).Copy Sheets("distribution1").Range("A" + CStr((CInt(LastRowInDistribution1) + 1))) 'i only wany the row (number), not the column (letter)
Sheets(CurrentDistribution).Delete

Now the problem is that I get this error when I try to do so: 'Run-time error '6': Overflow'. I used the debugger and found out its the 4th line which copies stuff that is throwing this error. Any idea whats the problem and how I can fix it?

Upvotes: 0

Views: 59

Answers (2)

Sahil Chaudhary
Sahil Chaudhary

Reputation: 503

Looks like my worksheet is so big that INT type can't handle my last cell number. So replace: 

Sheets(CurrentDistribution).Range("A4:" + LastCellCurrentDistribution).Copy Sheets("distribution1").Range("A" + CStr((CInt(LastRowInDistribution1) + 1))) 'i only wany the row (number), not the column (letter)

With

Sheets(CurrentDistribution).Range("A4:" + LastCellCurrentDistribution).Copy Sheets("distribution1").Range("A" + CStr(LastRowInDistribution1 + 1)) 'i only wany the row (number), not the column (letter)

Upvotes: 0

Gareth
Gareth

Reputation: 5243

I Imagine that you need to declare the variables LastCellCurrentDistribution LastCellDistribution1 LastRowInDistribution1 as long due to integers not being able to handle values greater than 32,000.

Try declaring them like below:

Dim LastCellCurrentDistribution As Long
Dim LastCellDistribution1 As Long
Dim LastRowInDistribution1 As Long

Upvotes: 1

Related Questions