Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

[making a macro] Almost there but....

Posted by Remi on February 06, 2002 7:01 AM
Hi,

I`m building a macro that pastes copied info in a new sheet. The new sheet expands continuously so therefore I scan the new sheet for the first empty row. Up to here my programme:


Sub scanpast()
screen = True
Range("A1").Activate
For r = 1 To 6666
If Len(Cells(r, 1)) = 0 Then
screen = False
Exit For
End If
Next
If screen = False Then
Range("A100:Z100").Select
Selection.Copy
[HOW?] Range("CStr(r)").Select [HOW??]
ActiveSheet.Paste
ActiveWorkbook.Save
MsgBox "PRIMA! Summary sheet is ge-update.", vbOKOnly
End If
End Sub


So, if the row is empty, (screen = false) I copy some info from cell a100 to z100 and I want to paste it in the empty cell I searched for in the above for loop (with r in it).

How do I get this done? It just does not work this way and the data gets pasted in cell a100 to z100.
Can anyone help on the way?


Thanks a lot.

Remi
Holland.


Check out our Excel VBA Resources

Re: [making a macro] Almost there but....

Posted by Tom Urtis on February 06, 2002 7:45 AM
May I offer a suggestion? Consider doing this task without a loop, to save time and system resources.

This code copies the entire row, where cells in column A are blank, over to another sheet.

Please note:

(1) Your code called for A:Z to be copied. If you don't care whether just A:Z or the entire row gets copied, or if your data range should expand in columns anytime down the road (happens a lot in business), this code will automatically accommodate that.

(2) The destination sheet reference in this example is Sheet2, which may or may not be "Sheet2" as you see it on your sheet tab. It is the VBA object reference to the sheet...works better in referencing code my opinion, once you get used to it. You can see these objects in the VBE's VBA Project Explorer.

(3) Maybe this should have been (1), but I hope I interpreted your post correctly, in that you are searching for blank cells in column A within your range, and wanting to copy over those rows. If I misunderstood, sorry.

(4) Modify the destination sheet name and set range as desired.


Sub CopyBlankRows()
Dim Rng As Range
Set Rng = Range([A2], [A6666].End(xlUp))
Rng.SpecialCells(xlBlanks).EntireRow.Copy _
Sheet2.[A65536].End(xlUp).Offset(1, 0)
End Sub


Again, just a suggestion for another approach.

Tom Urtis


Re: [making a macro] Almost there but....

Posted by Remi on February 07, 2002 12:21 AM
This code copies the entire row, where cells in column A are blank, over to another sheet. Please note: (1) Your code called for A:Z to be copied. If you don't care whether just A:Z or the entire row gets copied, or if your data range should expand in columns anytime down the road (happens a lot in business), this code will automatically accommodate that. (2) The destination sheet reference in this example is Sheet2, which may or may not be "Sheet2" as you see it on your sheet tab. It is the VBA object reference to the sheet...works better in referencing code my opinion, once you get used to it. You can see these objects in the VBE's VBA Project Explorer. (3) Maybe this should have been (1), but I hope I interpreted your post correctly, in that you are searching for blank cells in column A within your range, and wanting to copy over those rows. If I misunderstood, sorry. (4) Modify the destination sheet name and set range as desired.

Thanks for thinking along Tom.



This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.