th enumber of rows will changes every time I do this report I

[Darkchild]

HI fellow excel users! I have a situation that maybe you have an answer for. So I want to excel to look into one worksheet copy all data rows/but not empty rows and then paste them into the first empty row in another worksheet. I'd write a macro, but the number of rows to select will be different each time. I've been all over the web trying to solve this. I don't see anything that really addresses the fact that the amount of rows is going to be different each time I do this report: 2x a month. I'd like to have a formula, I think, rather than a macro. I'm only good at recording and not so much at code in VBA. Any suggestions would be very welcome.

I can provide a sample if you think you may have an answer
Thanks.

 

[icerabbit]

Have a look at the following:

Sub darkchild2()
'  darkchild2 Macro
'    Windows("source.xls").Activate
' use line above if source is open or line below to have excel open it - comment with ' as needed
    Workbooks.Open Filename:="C:\Users\reiziger\Documents\source.xls"
    Sheets("Sheet1").Select
    Range(Selection, Selection.SpecialCells(xlLastCell)).Select
    Selection.Copy
    Windows("destination.xls").Activate
    Sheets("Sheet1").Select
    ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select 'code to find first blank cell other than a1
    ActiveSheet.Paste
'   Closes the source file if it had to be opened and you want it closed, remove or comment out with ' if not needed
    Workbooks("source.xls").Activate
    ActiveWorkbook.Close

End Sub

 

[Darkchild]

Good morning. I was really thinking there may be a formula to do this. I've seen some rather complicated ones demo'd but haven't gotten around to trying it... since we last talked, I got everything on tabs in the same workbook. All the headings are formatted and the info is ready to move. If I have to do it manually it would be at least 96 cut and past operations before I can complete the report. What a Time eater that would be. anyway, gonna try the thing you sent yesterday...

Hope you have a good morning.

Dc

 

[icerabbit]

For 96 similar copy/paste operations, yes, an array would be great.

But, I did a 30+ part copy/paste macro the other day (copies 30 sheets into a summary within the same workbook, formats, filter, sorts, sums, ...) and it completes in mere seconds. Tested it with a limited # of sheets, recorded it, greatly simplified the recorded macro code, duplicated the copy lines with just changing the page number from 01 02 03 to 04 05 06 etc. So, it is just a small inconvenience of more lines of code to the programmer with the advantage of readability and reduced complexity. The computer has to iterate the copy code lines and do the physical copying in either method so I don't know that there is a time savings copy wise. I haven't practiced macros & VBA in many years, so I'd need to trial and error code.

Btw turning off screen updating throughout a larger macro vastly improves the speed.