VBA to copy formula from row above, then embed the data, then move on to the next row.

[RyanChampniss]

Hi,

I have a fairly complicated Excel file using lots of Index and Match formula to grab data from another file and format it to how I need it. There are 63 cells per row, with 41 containing formulas (22 without formulas). I need to copy all formulas and data from all 63 cells, except column A which contains my lookup value. A few of the cells have array formulas in as well.

I can use VBA to copy all the formulas down, then embed all the formulas, but when I run it, it locks up. There are 1000 rows in total. This is why I want to do it row by row, to hopefully speed it up.

Any idea how I can set specific columns to copy the formula down to the row below, then embed the data, then move on to the next row, until every row with data in column A has been processed? I'd also like the VBA script to be a button to update values should anything change on the source doc. A progress bar would also be amazing so I know it hasn't just frozen up!

Please help

 

[footoo]

What do you mean by : "then embed all the formulas" and "then embed the data"?
I think row by row will be slow.
Maybe the macro could be based on the following steps :
• In a blank column (say column 64) put sequential numbers from the first data row to the last
• Copy the data rows (entire row) and paste immediately below
• Go to the newly pasted rows, SpecialCells>Constants and clear contents (to remove constants but keeping formulas) - maybe not what you want.
• Sort all data by column 64
• Delete column 64

 

[RyanChampniss]

Thanks for the reply. Can you help me with a few bits?

• How can I copy the entire row, except column A? As I said, column A is where I will be pasting a reference code for the Index and Match formula to pull from.
• What is, and where can I find SpecialCells>Constants? I'm working on MacOS - does this exist in my version?
• Why would this method work better than processing each row individually?

FYI, this is for a book publishing business. We have an ISBN Log that contains a lot of information for all of our books. Some of these books need to be added to our website, but not all. My work flow will be:

• Add a list of new ISBNs in column A that need to be added to the site. Usually 40 or so at a time.
• Click a button to populate the fields (most of the formulas are Index and Match to grab the correct data from our ISBN Log, which has over 5000 rows.
• Embed the data in to the website spreadsheet.
• Save the spreadsheet as a CSV to upload to the website.
• If anything changes in our ISBN Log, I will need to click the button again to update the data.

 

[footoo]

Thanks for the reply. Can you help me with a few bits?

• How can I copy the entire row, except column A? As I said, column A is where I will be pasting a reference code for the Index and Match formula to pull from.
• What is, and where can I find SpecialCells>Constants? I'm working on MacOS - does this exist in my version?
• Why would this method work better than processing each row individually?

FYI, this is for a book publishing business. We have an ISBN Log that contains a lot of information for all of our books. Some of these books need to be added to our website, but not all. My work flow will be:

• Add a list of new ISBNs in column A that need to be added to the site. Usually 40 or so at a time.
• Click a button to populate the fields (most of the formulas are Index and Match to grab the correct data from our ISBN Log, which has over 5000 rows.
• Embed the data in to the website spreadsheet.
• Save the spreadsheet as a CSV to upload to the website.
• If anything changes in our ISBN Log, I will need to click the button again to update the data.

• Can just copy from column B the the last column .
• Sorry, didn't look at your profile. I'm not familiar with Mac. go to special excel mac - Google Search
• My suggestion is only a few steps using built-in Excel tools. Row by row processing (or cell by cell, or any other sheet object) is slow and should be avoided where possible on large data sets.

What do you mean by "embed"? Convert formulas to value?
I'm not sure what your data looks like or what you want to do.
I had assumed that you wanted to insert a row between each data row and then copy each row to the inserted row.