How to pull data into other columns when row #'s change between reports

**larryl19567** · 06-27-2019, 03:53 PM

book1.JPG
book2.JPG

Kind of lost on writing this macro, as I just began a few weeks ago. book1 and book2 images attached, and also workbook also attached to explain the below:

Sheet1 has customer information, including customer name, #, and all the related documents (invoice/payment info). The Customer ID and Customer Name are only at the heading of the information, and I need it to be pulled into each row to be included with the documents (invoice/payment info). However, the number of documents can change on a weekly basis, therefore, changing what rows all the data is on.

Basically, I need Sheet1 to end up looking like Sheet1(2). As previously stated, the number of rows per customer can change weekly, etc.

Is this even possible?

**Olly** · 06-28-2019, 05:49 AM

You can use Power Query to normalise the data from your source file:

Please Login or Register  to view this content.

Change filepath / name and worksheet name to suit.

**jindon** · 06-28-2019, 06:19 AM

VBA

Please Login or Register  to view this content.

**larryl19567** · 07-02-2019, 08:07 AM

Thanks Jindon, that works like a beauty. I do have one final thing I'm struggling with on this macro, and it will be completed. I need macro to calculate the Percentage of Total lines:

sheet4.JPG

Each "Percentage of Total" line needs to calculate the percentage of the Grand Total. For example, row 4 (columns G-L), needs to calculate =G3/G33, or =H3/H33, and so forth. However, as previously stated, the company totals, percentage of totals and grand totals' row numbers will change every time I run my report. The final "Percentage of Total" line located on row 34, is just calculating the percentage of the total (i.e. =G33/L33, or =H33/L33, etc.)

I have already used conditional formatting macro to format the percentage of total rows to 0.00% format.

Attached is excel workbook as well (see Sheet4)

**jindon** · 07-02-2019, 09:26 AM

Try

Please Login or Register  to view this content.

**larryl19567** · 07-03-2019, 08:37 AM

Error:

Attachment 630717

Attachment 630718

**jindon** · 07-03-2019, 09:14 AM

Don't quote full post unless you really need it to do, the forum doesn't like it.

No way to look at your attachment.

No error at all.

How to pull data into other columns when row #'s change between reports

LinkBack

Thread Tools

Rate This Thread

Display

How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Re: How to pull data into other columns when row #'s change between reports

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Pull-data-from-a-closed-workbook Format change VBA

[SOLVED] Pull data from one tab to another (but change how it's displayed)

Please Help - Summary/Reports (Multiple columns calculation into different reports)

Excel Reports - Using pull downs for multiple categories

[SOLVED] Query to pull out change and replace data

Pull Data from Multiple Columns Based On Data Validation List Selection

Change the data set your Vlookup's pull from Via a drop box

Bookmarks

Bookmarks

Posting Permissions