Macro for copying & inserting a variable number of rows into a separate workbook

**Gunther Maplethorpe** · 10-28-2013, 05:31 PM

I've got a workbook that I use for archiving data generated in another series of worksheets. I'd like to have a simple "Copy to archive" button that copies all of the non-empty cells in that worksheet (always a set number of columns, but varying number of rows) to the first empty row in the archive workbook. Is this possible, or would I have to settle for some ability to insert at a given point in the archive workbook, pushing the previously-entered rows down?

I've attached two worksheets as examples of what I'm wanting....the Origin and Archive worksheets. I'd like a button on Origin that copies all of the non-empty cells in A3:F200 to the first empty row in Archive; in this case, that would be copying A2:F5 in Origin to A6:F9 in Archive (Hope that makes sense.)

Archive.xlsm
Origin.xlsm

Bonus style points if the macro is commented such that a macro newbie like myself can understand it.

**cheeze83** · 10-29-2013, 05:16 AM

Morning!

Give this a go:

Please Login or Register  to view this content.

**Gunther Maplethorpe** · 10-29-2013, 11:20 AM

Thanks, cheeze. I'm especially grateful for the commenting - that will help me learn.

It was throwing an "subscript out of range" error for the line "Workbooks("Origin").Activate"; adding ".xlsm" to it fixed it, though (same thing later, when re-activiating the Archive workbook). So now all is working as expected!

I did discover that my example worksheets were missing an important feature of the "real" workbooks I'm using, and that is that the "real" workbook uses a series of formula to pull data from other parts of the workbook. (These formula start with a IF statement that evaluates to "" if certain criteria aren't met, so that only the important stuff shows up.) Is there a way around that, so that this macro isn't copying/pasting a bunch of formula or pseudo-empty cells (like ""), but is pasting only "actual" data?

**cheeze83** · 10-29-2013, 11:25 AM

just so I'm clear - all the cells in your Origin sheet have formulas in to ensure it gets the data needed, and the code is copying these as well into your archive with the formulas?

If that's right, if you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

although this would select everything in the range, including the formulas it would paste them as values, so the "" cells should be paste as blanks and not recognised as cells with content next time it's paste.....i think

**cheeze83** · 10-29-2013, 11:26 AM

just so I'm clear - all the cells in your Origin sheet have formulas in to ensure it gets the data needed, and the code is copying these as well into your archive with the formulas?

If that's right, if you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

although this would select everything in the range, including the formulas it would paste them as values, so the "" cells should be paste as blanks and not recognised as cells with content next time it's paste.....i think

**Gunther Maplethorpe** · 11-01-2013, 05:52 PM

Hrm...after changing only that one line, I get:

"Run-time error '1001':
Application-defined or onject-defined error"

BUT...changing it to

Please Login or Register  to view this content.

now it looks to be working swell!

**Gunther Maplethorpe** · 11-01-2013, 06:26 PM

Well, shoot...seems I spoke too soon. Though it's now pasting correctly - that is, it's not pasting formula from cells that return "" - but on successive pastes, it's seeing all those cells that were pasted blank, and skipping them all for the next paste.

In other words, the first time this macros is used, it pastes ten (say) rows of data, then another 30 (say) lines of what appear to be empty. Then, the next time I use this macro, it skips down to row 40 (after all the 'empty' cells) to paste in the next set.

How can I make those "empty" cells truly empty, so that they'er no0t 'seen' by the macro the next time?

Macro for copying & inserting a variable number of rows into a separate workbook

LinkBack

Thread Tools

Rate This Thread

Display

Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Re: Macro for copying & inserting a variable number of rows into a separate workbook

Thread Information

Users Browsing this Thread

Similar Threads

Inserting a variable number of rows throughout a catalog.

Copying a variable number of rows in a macro

macro inserting and copying rows

[SOLVED] Copying range to variable number of rows

Inserting Variable Number of Rows

Bookmarks

Bookmarks

Posting Permissions