Copy / Paste dynamic Data using VBA

[rz6657]

I maintain several accounts with different status points. Instead of having 1 file per each, I've managed to merge them into 1 file. However, the last obstacle I'm trying to resolve is to have also have one summary best view in tab vs. several tabs. I've managed to solve the financial numbers per account. Once I select the "account number", the financial numbers will appear. However, the issue is ensuring I retain the manually edited status items and numbers. Those areas are highlighted in Gold accent.

My approach was as I select a distinct account (H3), I want the current data to be copied/pasted to the Data tab under it's account section and than copy/paste any data from the newly selected account number from the Data tab to the View tab. However, my issue is how will the code know where to copy to and what account number was selected and copy that data only out of the 7 accounts....

Any ideas on how to accomplish that would be greatly appreciated...

[LJMetzger]

Hi rz6657,

Your request is relatively easy to do, because your data is consistent and well organized. See the attached file which is an updated copy of your workbook that contains the following code.

How the Software Works:
a. Macro PasteFromSheetDataToSheetView() in Code Module ModPasteData is triggered:
(1) from Sheet 'View' Macro Worksheet_Activate() whenever Sheet 'View' is selected (to update the data if a change was made on Sheet 'Data').
(2) from Sheet 'View' Macro Worksheet_Change() whenever the 'Account Number' is changed.
b. Macro PasteFromSheetDataToSheetView() does the following:
(1) Reads the Account Number on Sheet 'View'.
(2) Finds a matching Account Number (CASE INSENSITIVE) on Sheet 'View'.
(3) Determines what Column the 'Account Number' is in on Sheet 'View'.
(4) Copies the data from Sheet 'View' to Sheet 'Data' by knowing the relationship of the Account Number Column to the Data Columns on Sheet 'View'.

There are a lot more efficient ways to do what you want with a lot less code, but this method is relatively easy to read and to understand and to maintain.


In the Sheet 'View' code module:

Please Login or Register  to view this content.

In the ModPasteData Code module:

Please Login or Register  to view this content.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

To access a Sheet Module in VBA:
a. 'Left Click' on any cell in the Excel Spreadsheet.
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. 'Double Click' on the module you want to access in the 'Project Explorer'.
e. Insert code into the module if needed. 'Option Explicit' should only appear ONCE at the top of the module.

Lewis