Copy Until Done (Excel 97 SR2)

Topic

New Reply

In the attached spreadsheet, I need to copy cells E through P and R through V for each row that has a total in Q AND put that information in the next available row in the upload tab starting in F3. It would have to exclude the rows for TOTAL REVENUE, EXPENSE, and NET.

There could be more/less than 55 rows if the user inserts/deletes rows.

Also, I need to do this for each budget sheet in the workbook, the number of which could vary by user.

The Upload tab is formatted for uploading to our GL and can

Reply | Quote

Replies

Sherry,

Does this code work?

Sub TransferData()

Dim PickRowVal As Double
Dim PutRowVal As Double
Dim testme As Variant
PickRowVal = 12
PutRowVal = 3

Do While Worksheets(“budget 1”).Cells(PickRowVal, 1).Value “TOTAL REVENUE”

testme = 0 + Worksheets(“budget 1”).Cells(PickRowVal, 17).Value

If testme 0 Then

Worksheets(“budget 1”).Select
Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worksheets(“budget 1”).Select
Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PutRowVal = PutRowVal + 1

End If

PickRowVal = PickRowVal + 1
Loop

Do While Worksheets(“budget 1”).Cells(PickRowVal, 1).Value “EXPENSE”
PickRowVal = PickRowVal + 1
Loop

Do While Worksheets(“budget 1”).Cells(PickRowVal, 1).Value “TOTAL EXPENSE”

testme = 0 + Worksheets(“budget 1”).Cells(PickRowVal, 17).Value

If testme 0 Then

Worksheets(“budget 1”).Select
Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worksheets(“budget 1”).Select
Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PutRowVal = PutRowVal + 1

End If

PickRowVal = PickRowVal + 1
Loop

End Sub

I would however, be careful with the condition for transferring a line – as I’m sure you’re aware, a full year budget of zero does not mean there is no movement on the account.

Brooke

Reply | Quote

Brooke,

It works like a charm, and I understand MOST of it. I follow everything that happens up to the first LOOP. And I understand the third DO..LOOP. What I don

Reply | Quote

Try the following: it seemed to work ok. It’s kind of you to comment on the efficiency, but there is a fair bit of tidying up that could be done here.

Brooke

Sub TransferData()

Dim PickRowVal As Double
Dim PutRowVal As Double
Dim testme As Variant
Dim ws As Worksheet

PutRowVal = 3

For Each ws In Worksheets
Msgbox ws.Name
If ws.Name “upload” Then

PickRowVal = 12

Do While ws.Cells(PickRowVal, 1).Value “TOTAL REVENUE”

testme = 0 + ws.Cells(PickRowVal, 17).Value

If testme 0 Then

ws.Select
Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ws.Select
Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PutRowVal = PutRowVal + 1

End If

PickRowVal = PickRowVal + 1
Loop

Do While ws.Cells(PickRowVal, 1).Value “EXPENSE”
PickRowVal = PickRowVal + 1
Loop

Do While ws.Cells(PickRowVal, 1).Value “TOTAL EXPENSE”

testme = 0 + ws.Cells(PickRowVal, 17).Value

If testme 0 Then

ws.Select
Range(Cells(PickRowVal, 5), Cells(PickRowVal, 16)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 6), Cells(PutRowVal, 17)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

ws.Select
Range(Cells(PickRowVal, 18), Cells(PickRowVal, 22)).Copy
Worksheets(“upload”).Select
Range(Cells(PutRowVal, 18), Cells(PutRowVal, 22)).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

PutRowVal = PutRowVal + 1

End If

PickRowVal = PickRowVal + 1
Loop

End If

Next

End Sub

Reply | Quote

Brooke,

It works great AND I have learned something!

Thanks a lot!

Reply | Quote

No problems. I didn’t answer your question though – i put the middle loop in purely because you said users might be adding or deleting rows and so needed to be sure that I was in the right place before starting copying data over again.

Brooke

Reply | Quote

I think I’m a little fuzzy on the DO..LOOP statements. Will a LOOP continue indefinitely if there isn

Reply | Quote

No and yes to the first question. I haven’t got an exit loop in there, but it exits when the condition after while is met. To illustrate this, when I wrote the above, for my first trial I had forgotten to add the line pickrowval = pickrowval + 1, so after a minute or two with the code still running, I knew I’d done something wrong. pickrowval was still at 12 and so the condition was never met and the loop would run for ever.

for the second question, the basic structure here is

Do While (condition)
(do stuff)
loop

so each loop finishes before the next one starts. However, you can nest loops, eg

Do While (condition)
(do stuff)
	Do While (condition)
	(do stuff)
		Do While (condition)
		(do stuff)
		loop
	loop
loop

but here I haven’t.

There are other ways of looping eg

For Each letter In alphabet
….
Next

For letter = a To z
…..
Next

and I didn’t necessarily choose the best method here.

HTH

Brooke

Reply | Quote

Amazing, I understand . I really do.

Thanks a million!!!

Reply | Quote

Hi All,

Just a very picky point, ignore if you wish. If PickRowVal and PickRowPut are row numbers (versus cell values), it is a bit much to declare them as Double (double-precision floating point). A better choice would be Long (long integer), which can still account for the maximum number of rows in a sheet.

Reply | Quote

Your point is valid. I just use the rule of thumb that if it’s a number then it’s a double. I guess I picked that up from a thread here back around march/april (ish) where the advantages of using different data-types was discussed. Only after the code is written and done the job and there’s an outside chance that I might keep it and reuse it in the future do I even think about cleaning it up – and it very rarely happens even then!

Brooke

Reply | Quote

I only vaguely understand why you declare a variable as a certain type. I looked up the Data Types in VB Help, but I can

Reply | Quote

Technically speaking it should do but when I pulled the code together for you it was falling over – I wanted to see what value testme actually was – by putting that line in and pausing the code I could see what value it held – (it was coming up empty for some reason) similarly, the 0 + testme is my way of forcing a type conversion to make sure the result is numeric – you can do this by Cdbl(testme) but old habits die hard…….

Brooke

Reply | Quote