Indirect? or Match? ??

[bg]

I have a project I need to get out tomorrow and have a couple of steps to go
but similar in nature.

I tried to post earlier but it didn't post for some reason?

I have 6 tabs
Tab 1 - Data Entry
Tab 2,3,4,5 - have tables that are created (medical claims, rx claims,
vision claims etc...)
Tab 6 - Summary of all combined

I need to look at Tab 1 and take the value they choose in Cell A12 and match
that name with a tab and copy the table from I7:N20 and put it into the Tab 6
below a bunch of other summary data

for example:
Tab 1 Cell A12 = Medical
So I want it to go to Tab 2, the name of the tab is Medical Claims so it
would choose the table in I7:N20 and copy and place into the Summary tab

How do I do this automatically .. with my other summary data. ?? Is this an
indirect function or?? thank you all in advance!!!!

 

[Jacob Skaria]

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>

Few points to be noted
--Tab names are cell A12 and ** a ** space followed by 'claims' example :'
Medical claims'
--You havent mentioned the destination cell; which is assumed as Column A
next available free cell..Adjust to suit. Try and feedback

Sub CopyMacro()
Dim lngNextRow As Long
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Data Entry")
Set ws2 = Worksheets(CStr(Trim(ws1.Range("A12")) & " claims"))

lngNextRow = ws2.Cells(Rows.Count, "A").End(xlUp).Row
ws1.Range("I7:N20").Copy ws2.Range("A" & lngNextRow)
End Sub


If this post helps click Yes

 

[bg]

thank you Jacob :> I'm very new to this and a little confused?
I have some code written for the prior data in the Summary tab and was
guessing I add .Offset(2,0).FormulaR1C1 = ????? But I wasn't sure what that
part says

in the example you helped me with below could you help me understand how it
knows to go to the Medical Claims tab vs the other 3 and then I'm not sure
how it get's pasted into the Summary tab (I was guessing, but truly this is
only a guess that it somehow goes into the >offset?

-- Brad

 

[Jacob Skaria]

Here we are using the worksheet object..

The below line will assign ws2 as a worksheet object named as
ws1.Range("A12") & " claims"

Set ws2 = Worksheets(CStr(Trim(ws1.Range("A12")) & " claims"))


If this post helps click Yes