PDA

View Full Version : VBA Macros to Copy Data to Another Workbook



Sophia
10-17-2019, 02:08 PM
Hi Guys,

I need help with transfering data from one Multi sheet Workbook with "not fixed" Sheet name (change each time) using Macro


Steps:


I need to add date from cell G6 (will stay fixed for all sheets) from Workbook A to Workbook B column A
Payment method from Column E from Workbook A will go to Column B Workbook B
Then to add doc_num from cell G5 (will stay fixed for all sheets) from Workbook A to Workbook B column C
Most complicated part is transferring amount first of all column A B C of Worksheet A need to be combined in this format A-B-C (example 1-4111-702) and then match against row 2 in Workbook B and add Amount from column H of Workbook A under relevant cell
If same code repeated few times it is better to add total value as in highlighted in cell F3
If Payment method is different new line needs to be created as in example row 5 and 6
This is continuous process file need to be updated (next info need to be added to new row under pervious data) each week for reconciliation so every week new Workbook will be received with new multiple sheet
Amount in column F workbook A need to be ignored
Sort new data upload by date Ascending order


Note files are attached.

Thanks in advance
Sophia

大灰狼1976
10-17-2019, 08:56 PM
There are two questions:
1. Is the data of E1:Q2 range of workbookB original or generated in the process.
2. Why "2-4440-901" appears twice (K2 and O2).

Sophia
10-17-2019, 09:44 PM
There are two questions:
1. Is the data of E1:Q2 range of workbookB original or generated in the process.
2. Why "2-4440-901" appears twice (K2 and O2).

Hi thank you for your question

1. no I adjusted it manually this is not system report.
2. It is mistake should be one time only, just consider it like one.

大灰狼1976
10-18-2019, 02:15 AM
Hi Sophia!
I'm sorry to be off work. Wait until I have time.

Paul_Hossler
10-18-2019, 06:54 AM
Off this weekend sitting the grandkids -- I'll check this when I get back

Sophia
10-18-2019, 07:53 AM
Mornig,

Thank you Paul
I need to close this case today, please help me who are able today.
Happy Friday

paulked
10-18-2019, 07:01 PM
Demand not met, me thinks!

大灰狼1976
10-20-2019, 06:43 PM
Hi Sophia!
Please refer to the attachment. Macro in Workbook B.

Sophia
10-21-2019, 01:43 PM
Hi VBAX Mentor,

Could you check please there is no Macro in Workbook B :(

paulked
10-21-2019, 02:27 PM
There is, in Sheet1 module!

Sophia
10-21-2019, 02:49 PM
I think problem is that I have protected view