Transfer of data between workbooks with reference to Worksheet name?

laughterbattle

New member
Joined
Jul 19, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
Office 2016
Hi all - I'm completely new to using VBA and macros and I am wondering if it can be used to improve a data entry process that is currently done manually.
Basically, the process is roughly as follows:
· Workbook1
o Several Worksheets (A, B, C etc.)
o Each Worksheet has data for individual companies in a standard format

· Workbook2
o Several Worksheets with the same naming convention relating to the same companies as in Workbook1
o Each Worksheet has blank cells into which data from the corresponding Worksheet in Workbook1 must be entered

· For each ‘company’, what is effectively being done under a manual process is a VLOOKUP for the value in each Worksheet in Workbook1so that the values are displayed in each Worksheet in Workbook2.
My question is, could a VBA macro automate this process to update each Worksheet in Workbook2 by referring to the corresponding sheet in Workbook1 which has the same name? I hope it can, but can't seem to find any online materials which describe how to program this specifically.
Any help is much appreciated!
 
Last edited:
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


Remember to desensitize the data.
 
I cannot understand the rationale behind what you are saying, it sounds to me that the second workbook will end up looking exactly the same as the first one.

Also, it is a bad practice to have multiple sheets, one per company. You should merge them all into one and then select which one you want to look at in a separate view, say a pivot, or a formula driven dashboard.
 
Back
Top