[RESOLVED] Stranger Problem with copy data from sheet to another
I tried this code to copy the data from the sheet1 to "bills" sheet
it must starts copy in the third row in the "bills" sheet. but it starts form another row and every time I delet the data in "bills" sheet it starts from the new row. Take a look to the example I uploaded. How to fix this problem?
Code:
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range, myRange4 As Range
Dim myRange5 As Range, myRange6 As Range, myRange7 As Range, myRange8 As Range
Dim myRange9 As Range, myRange10 As Range, myRange11 As Range, myRange12 As Range
Dim j As Integer
Set myRange1 = Sheets(1).Range("A24:A1000")
Set myRange2 = Sheets(1).Range("B24:B100")
Set myRange3 = Sheets(1).Range("C24:A1000")
Set myRange4 = Sheets(1).Range("D24:B100")
Set myRange5 = Sheets(1).Range("E24:A1000")
Set myRange6 = Sheets(1).Range("F24:B100")
Set myRange7 = Sheets(1).Range("G24:A1000")
Set myRange8 = Sheets(1).Range("H24:B100")
Set myRange9 = Sheets(1).Range("I24:A1000")
Set myRange10 = Sheets(1).Range("J24:B100")
Set myRange11 = Sheets(1).Range("K24:A1000")
Set myRange12 = Sheets(1).Range("L24:B100")
j = Sheets("Bills").UsedRange.Rows.Count + 1 + Sheets("Bills").UsedRange.Row
myRange1.Copy Sheets("Bills").Range("a" & j)
myRange2.Copy Sheets("Bills").Range("b" & j)
myRange3.Copy Sheets("Bills").Range("c" & j)
myRange4.Copy Sheets("Bills").Range("d" & j)
myRange5.Copy Sheets("Bills").Range("e" & j)
myRange6.Copy Sheets("Bills").Range("f" & j)
myRange7.Copy Sheets("Bills").Range("g" & j)
myRange8.Copy Sheets("Bills").Range("h" & j)
myRange9.Copy Sheets("Bills").Range("i" & j)
myRange10.Copy Sheets("Bills").Range("j" & j)
myRange11.Copy Sheets("Bills").Range("k" & j)
myRange12.Copy Sheets("Bills").Range("l" & j)
Re: Stranger Problem with copy data from sheet to another
used range is not reset when cell content is deleted, so if you delete the content of the last 2 rows, you would get 2 additional blank rows, if you delete (or cut) 2 rows i believe then the usedrange would be correct
also you don't need to set the ranges then copy, you can just copy the range
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Re: Stranger Problem with copy data from sheet to another
Originally Posted by westconn1
used range is not reset when cell content is deleted, so if you delete the content of the last 2 rows, you would get 2 additional blank rows, if you delete (or cut) 2 rows i believe then the usedrange would be correct
Re: Stranger Problem with copy data from sheet to another
even if you delete the values from some rows of cells, they are still used
at least until the workbook is saved and closed
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
[RESOLVED] Stranger Problem with copy data from sheet to another
Reply With Quote
Originally Posted by westconn1
