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

RyanChampniss

New Member
Joined
Apr 8, 2020
Messages
2
Office Version
  1. 365
Platform
  1. MacOS
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 :)
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top