Lookup and paste special on multiple sheets

[coco803]

I have a file with multiple sheets. Most of the sheets are named for states. I will import a worksheet monthly that has sales data that needs to be copied one line at a time and pasted to the correct company on the sheet that corresponds to the appropriate state. I want to do this by macro. Here is what my sheets look like...

Arkansas
A B
Comp1 $1000
Comp2 $2000

Louisiana
A B
Comp3 $500
Comp4 $1000

Sheet3 - Import
A B C
Comp1 AR $500
Comp3 LA $500
Comp2 AR $500

What I want is a macro that will read each line in Sheet3 and depending on the state in column B, copy/paste/special/add the value in column C to the amount in column B for the corresponding company on the sheet for that state. In other words I would like to see..

Arkansas
A B
Comp1 $1500
Comp2 $2500

Louisiana
A B
Comp3 $1000
Comp4 $1000

I am a pretty new at macros so I don't know how complicated this is. I don't really know where to start. I tried recording the macro but because of the lookups across several sheets, I am not having any luck. Can anyone at least show me some code for doing lookups while looping through the import data?

 

[westconn1]

For i = 2 To Sheets("sheet3").UsedRange.Rows.Count
    sname = WorksheetFunction.VLookup(Cells(i, 2), Range("d1:e99"), 2) 'find sheet name from table of abbreviations
    a = Sheets(sname).Range("a:a").Find(Cells(i, 1)).Address  ' find address of comp1 etc
    Sheets(sname).Range(a).Offset(, 1).Value = Sheets(sname).Range(a).Offset(, 1).Value + Cells(i, 3).Value  ' update value on sheet
Next

i had to make up a table of the state abbreviations to find the sheet names, i put this in range d1 to e99, but you can put in a separate sheet if you wish, just change the range to suit

 

[coco803]

Thanks Westconn1!
I am getting some errors but I think I can work through them. I actually do have the state list in another sheet for lookup. It is a named range so I am trying to work that into the code. I will also probably need to add something for when it doesn't find the company. Probably OnError, right? At that point I would redirect to an AddNewCompany macro that is already working. I just need to figure out how to get back to the Import macro I started with. I am going to make an attempt to do this on my own but may be more than my skills allow.

 

[coco803]

Okay, an hour and a half later I have made no progress. I get an error that says "unable to get the Vlookup property of the Worksheet Function class". The debugger hightlights the sname=WorksheetFunction line. The only changes I made were to replace the range address with a range name and I changed the lookup reference to Cells(i,4) because I moved the state abbreviations in Sheet3 to column 4. This is a copy of my code

For i = 2 To Sheets("Sheet3").UsedRange.Rows.Count
'find sheet name from table of abbreviations
sname = WorksheetFunction.VLookup(Cells(i, 4), Range("statelookup"), 2)
' find address of company
a = Sheets(sname).Range("a:a").Find(Cells(i, 1)).Address
' update value on sheet
Sheets(sname).Range(a).Offset(, 12).Value = Sheets(sname).Range(a).Offset(, 12).Value + Cells(i, 7).Value

Can you help me past this error?

 

[westconn1]

you need to specify the sheet that contains the named range

 

[coco803]

Yeah I did that. It still isn't working.

Dim i As Long
Dim sname As Worksheet

For i = 2 To Sheets("CapAmerica").UsedRange.Rows.Count
'find sheet name from table of abbreviations
sname = WorksheetFunction.VLookup(cells(i, 4), Sheets("info").Range("statelookup"), 2, False)
On Error GoTo NewAccount
' find address of company
a = Sheets(sname).Range("a:a").Find(cells(i, 1)).Address
' update value on sheet
Sheets(sname).Range(a).Offset(, 12).Value = Sheets(sname).Range(a).Offset(, 12).Value + cells(i, 7).Value
NewAccount:
Msg = "Add New Account?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then FormAddNew.Show

Next
End Sub

I tried replacing the named range with the cell reference, I tried declaring all my variables, some of my variables, etc. I tried moving the state abbreviations to a different column in the import file and changing the cell(i,x) reference. I entered a vlookup formula in the worksheet (=vlookup(d2,statelookup,2,false)) and it worked fine so I know my named range is correct and the lookup value exists in the named range. I have been on this one line all day and nothing works. I can't even see if the rest of the macro will work. I have to move on. I will find some sort of workaround.

Thank you so much for your help, I am just not to a point in macros where I can figure this out in the time I have.

 

[westconn1]

what happens error or no result?
does sname get any value?

you could convert to use find for the abbreviation and get the value from the offset of 1 column

 

[westconn1]

if the sheet capamerica is not the active sheet, you would need to specify the sheet name for all ranges (and cells)

 

[coco803]

I am getting an error on the sname=worksheetfunction line. I get an error that says "unable to get the Vlookup property of the Worksheet Function class". I don't know if sname is getting an error because the code stops there. I am not sure what you mean by specify the sheet name for all ranges and cells. Hasn't "i" been defined as the usedrange in Capamerica? And when I set up the range name statelookup, the address included the sheet it is on. But even so, I specified the sheet name for statelookup in the code and it still didn't work.

 

[coco803]

okay, I added the sheet name to the lookup and now I get a different error. It says "Object variable or With block variable not set". I have no idea what that means. Like I said, this might be beyond me. I am working on a workaround. Not efficient and I will have to lock and protect a lot of cells to keep the users from destroying but I need to get this done.


Dim i As Long
Dim sname As Worksheet

For i = 2 To Sheets("CapAmerica").UsedRange.Rows.Count
'find sheet name from table of abbreviations
sname = WorksheetFunction.VLookup(Sheets("capamerica").cells(i, 4), Sheets("info").Range("statelookup"), 2, False)
On Error GoTo NewAccount
' find address of company
a = Sheets(sname).Range("a:a").Find(cells(i, 1)).Address
' update value on sheet
Sheets(sname).Range(a).Offset(, 12).Value = Sheets(sname).Range(a).Offset(, 12).Value + cells(i, 7).Value
NewAccount:
Msg = "Add New Account?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbYes Then FormAddNew.Show

Next
End Sub

Thanks again Westconn1.