Hopefully the following is fairly self explanatory

I am trying to copy data from one worksheet to another.

Problem is everytime i run the function all that happens is that the data is copied from the same row on one worksheet to the same row on the other. I am guessing my use of the copy destination is wrong?

Sub Workbook_Open()
Dim rowcountold As Integer
Dim t As Integer

‘count the number of rows
rowcountold = Worksheets(“page”).UsedRange.Rows.count
‘Loop through counting the rows and each that matches the criteria copy to another worksheet
t = 1
For i = 1 To rowcountold

If Cells(i, 1).Value = “Cash Sale B80” Then Rows(i).Copy Destination:=Worksheets(“Sheet1”).Rows(t)
t = t + 1
rowcountold = rowcountold + 1

Next

End Sub

copy and paste special

copy paste won’t work when you have 60,000 lines and you only want some of them.

I need to look at the first column, if the contents match what i want then copy the entire row to another worksheet. starting at row 1 and going from there.

have you tried a conditional forumla with Indirect?

again not an option, the above is me trying to understand the code, in reality their are 60 different conditions depending on the match to be copied to 10 different worksheets,

I can’t see a way of avoiding VBA, i can carry on as i am doing which is pivot tables and cut and paste. But i thought if i can get this right it will save time and help me learn a bit more in the process.

I can get it to work, i just don’t understand why it won’t paste into the first row but into the same row in the new worksheet as the data came out of the old worksheet.

=IF(INDIRECT(“Sheet2!”&CELL(“address”,$A1))=”Cash Sale B80″, INDIRECT(“Sheet2!”&CELL(“address”,A1)), “”)

I cant help with the VBA Im afraid, but this may be of use ^

sorted

Sub Workbook_Open()
Dim rowcountold As Integer
Dim t As Integer

‘count the number of rows
rowcountold = Worksheets(“page”).UsedRange.Rows.count
‘Loop through counting the rows and each that matches the criteria copy to another worksheet

t = 1
For i = 1 To rowcountold
If Cells(i, 1).Value = “Cash Sale B80” Then
Rows(i).Copy Destination:=Worksheets(“Sheet1”).Rows(t)
t = t + 1
End If
rowcountold = rowcountold + 1

Next i

End Sub

just the “End if” missing?

What is this line for?

rowcountold = rowcountold + 1

Doesn’t it create a never ending loop (as the end figure increases by one each time the counter i increases)

mb, a bit of junk that i hadn’t deleted that seems to have no effect?

I think the problem was with the t=t+1 it would always count because it was outside the if then loop. by splitting the if statement and using the end if i can get more if then options.

I think….

ok for the development of this problem. I am sure there are variables defined that i don’t need and variable undefined…

This works as i want it too, bar one issue, because “A” counts across all the 8 worksheets i have set up, i have a lot of empty rows.

Suggestions as to the best way of removing these empty rows.

As i see it two ways, i can get the data copied to the right cell in the first place.

So i need to d something to

Worksheets(“data”).Rows(i).Copy Destination:=Worksheets(region).Rows(A)

Or i clean the data afterwards and remove the empty cells.

Ideas gratefully received!

Sub OrganiseData2()
Dim rowcountold As Integer
Dim i As Integer
Dim region As String
Dim companyname As String
Dim ws As Worksheet
Dim rng As range
Dim z As Integer

Set ws = Sheets(“accounts”)
Set rng = ws.range(“a2:c200”)

‘count the number of rows
rowcountold = Worksheets(“data”).UsedRange.Rows.Count
‘Loop through counting the rows and each that matches the criteria copy to another worksheet

A = 4
For z = 2 To 117
For i = 1 To rowcountold
companyname = Worksheets(“accounts”).Cells(z, 1).Value
If Cells(i, 1).Value = companyname Then
region = Application.WorksheetFunction.VLookup(companyname, rng, 3, 0)
Worksheets(“data”).Rows(i).Copy Destination:=Worksheets(region).Rows(A)
A = A + 1
Else: End If

Next i
Next z

End Sub

Or i clean the data afterwards and remove the empty cells.

doesnt hurt my brain as much… 🙂

I know what you mean…my head hurts from getting this far. Be nice to get the whole problem solved with code, just so i know how.

If you are able to send me the spreadsheet + vb I’ll have a look. What you are trying to do looks fairly straight forward but it’s a bit difficult to visualise the data layout and how it all relates to be sure.

You could just replace A with a last Row function.

i.e Worksheets(“data”).Rows(i).Copy Destination:=Worksheets(region).Rows(Worksheets(region).UsedRange.Rows.Count + 1)

So it gets the last row everytime…

pp, thanks for the offer, if you let me know your email, i’ll send it from work tomorrow,

My email is nick.welsh@hotmail.co.uk

Sorry mrmo my email is nick_welsh@hotmail.co.uk !!!