Transfer data between sheets

[ken78]

***PLEASE NOTE*** I have just tried to do some of this manually and I have realized that descriptions are NOT the same for each item (the person I am helping told me they were - that's why I thought this was possible). Please see post 7.

I have a massive spreadsheet that I am developing and I am in need of a quick way to transfer values.

I have several columns in Sheet1, but I can copy and paste to where I only need 2 columns.

In sheet 2 I have several columns. For every 1 value that I need copied from sheet 1, I have 4 places where it could go, this is where I am totally confused. The place it needs to go is determined by 3 different columns.

This is what I am trying to do:

Sheet1
ColumnA ColumnB
xyz 123
xxx 456

Sheet2
ColumnA ColumnB ColumnC ColumnD
xyz abc mno 123
xyz def pqr
xyz ghi stu
xyz jkl vw

xxx aaa bbb 456

I hope this makes sense. Anyway, I would be greatful for some help.


ColumnB in sheet2 is usually something like this:

PE0D0
PE0D0
PE0E0
PE0E0

ColumnC in sheet2 is like this for every value:
ORBC
ORBQ
ORBC
ORBQ

ColumnD is where I would need the value.

Right now I need to plave a value in the cell next to PE0E0 and ORBQ.

After one is complete I could figure out how to do the rest.

[Jacob Hilderbrand]

Can you attach an example workbook (zip it first)? That way we could get a better idea of what you are doing.

[Killian]

Hi there and welcome to VBAX :hi:

I'll be honest, it doesn't make a lot of sense (tho, that could just be me). I kind of get a vague idea of what you want to do but I can't work out the criteria for what needs to be applied and programming is all about working out rules for things.
The best way to work out what code to write is to set out your rules in "pseudo-code", for example:
with all the cells used in sheet1 column1
if its value is less than 500 then it goes in the corresponding row of sheet2 column3
otherwise it goes in the corresponding row of sheet2 column4

This would translate roughly to something like


Dim c As Range
With ActiveWorkbook
For Each c In .Sheets(1).Range("myNamedRange").Cells
If c.Value < 500 Then
.Sheets(2).Cells(c.Row, 3).Value = c.Value
Else
.Sheets(2).Cells(c.Row, 4).Value = c.Value
End If
Next
End With

The translation to VB is quite straight forward - the hard work is nailing the rules and criteria and the trick is to get that worked out before you worry too much about how to code it.

I hope this helps you some... if you can get us the specifics of how you want it to work, there's plenty of people here happy to help you code it! :thumb

[ken78]

Thanks for replying guys. I know you guys are leet when it comes to this stuff and I have basically no clue. I am trying to help out someone who is tasked with a rediculous amount of work that is due Friday - I was hoping to have everything done by tomorrow evening (CST here). Anyway, I have a tiny bit of VBA experience - the only small bit I know I picked up from a co-worker while we were stationed at a job site for a couple of months with not too much to do. I figured I would try to automate the process to get the work done, but ran into problems immediately since I have not looked at VBA in about half a year. I will put together a sheet that explains what I am trying to accomplish.

OK, I have made a sample workbook that contains 3 sheets. I hope this explains it better. It also gets more complicated :dunno

There is one sheet with desciption, codes, and values. These codes need to be matched with a correspoding description in another sheet. Then the codes referenced in the other sheet need to feed the final sheet with corresponding values. Hopefully you will understand what I am trying to do when you look at the workbook.

I just realized my sample sheet did not have correct corresponding 'ORBC' values - please ignore that lol.

This is way too complicated for me, but hopefully you guys can help. It would save a whole bunch of looking up and down spreadsheets trying to match value and inputing them into the other program. There are close to 12,000 values that need to be entered!!! I seriously don't think 2 people can do that in one day.

[ken78]

Hi there and welcome to VBAX :hi:

I'll be honest, it doesn't make a lot of sense (tho, that could just be me). I kind of get a vague idea of what you want to do but I can't work out the criteria for what needs to be applied and programming is all about working out rules for things.
The best way to work out what code to write is to set out your rules in "pseudo-code", for example:
with all the cells used in sheet1 column1
if its value is less than 500 then it goes in the corresponding row of sheet2 column3
otherwise it goes in the corresponding row of sheet2 column4

This would translate roughly to something like


Dim c As Range
With ActiveWorkbook
For Each c In .Sheets(1).Range("myNamedRange").Cells
If c.Value < 500 Then
.Sheets(2).Cells(c.Row, 3).Value = c.Value
Else
.Sheets(2).Cells(c.Row, 4).Value = c.Value
End If
Next
End With

The translation to VB is quite straight forward - the hard work is nailing the rules and criteria and the trick is to get that worked out before you worry too much about how to code it.

I hope this helps you some... if you can get us the specifics of how you want it to work, there's plenty of people here happy to help you code it! :thumb

I tried out this little script but it does not work, it says -'application-defined or object-defined error'

I was going to see if I could work something out from using this code you have posted. :dunno

[Killian]

umm no... that was just an example to illustrate the point about how you need to work out the rules and critera for what you want to do.
I had a look at the workbook but I'm still not clear how this is supposed to work. It would be good if you could desribe the manual process step-by-step for one iteration (then, i guess we just loop it 25000 times or whatever)
For example: On Sheet3, starting with row 2, get the value in ColA, check it against the data in sheet2 to find the correct "Code" (sheet2, colB) and insert that value back on Sheet3 in ColF
You might have to add some contidional statements like:
If the value in "Code 2" in Sheet3 colB is ORBC, use the code from another (specify) location, or if it's ORBD, ignore it etc etc etc

That's how we can work out what to do, from a start to finish step-by-step process. that we can refine and the loop for however many rows of data there are.

I'm finishing work now but I'll check in later tonight

[ken78]

I have several worksheets that contain data similar to sheet1.

The first step is to look at 'Column E' in Sheet1. Here you see a 'DescriptionA' This 'DescriptionA' then needs to be found in Sheet2. I need to take the code 'P2110042' (for example) for 'DescriptionA' and place it in a column in Sheet1, next to the wrong code. Then I need to take the value under (for example) column B in Sheet1 and put it in 'Sample Final Sheet' under ColumnF titled value, next to the respected Code1, Code2, and ORBQ. I had a little help from someone, but it does not quite work. I will post what they did.

I can make it easier by copying and pasting data from from Sheet1 into 2 columns - Just the description and numerical value - then a third column would be needed for the correct code. This is probably the main step I need right now - getting the codes to match the descriptions. I might be able to 'wing it' with the formula this other guy gave me. It takes forever to run the formula on all the sheets though - I am thinking VBA could do it faster.

This is the sheet that another guy helped with. He wrote a formula that gets a value. This is a sheet where I just copied and pasted values and correct codes.

*******UPDATE**********
The person I was working with mislead me. My other alternative is to manually put correct codes next to the descriptions, which could take a while because I am having problems matching them manually because they are so different. THEN run a script where the correct codes are matched with values and put into the final sheet.

[Killian]

OK, a vague picture is starting to form in my head...
I've attached a work book that works like this:
The first sheet is the data you want to process. The other sheets hold data to look up the correct values (I've changed it a bit so I can see if it works). The code behind goes through each row on the datasheet.
With each one, it checks if it's ORBQ - if it is, it gets the value in colA (P2110042) and looks at the look up sheet for the colB value (PE0D0), retrieves the target value (1024) and places it in colD of the datasheet.

Open the workbook and run the marco (hit Alt+F11 to see the code - Module1)
I don't think this is exactly what you want but it's a start, I hope..?

[ken78]

This really does look like it is going to help. I added some more values and changed the range to test it out and it works - thank you so much - you are leet :beerchug:

I see that you can rename the sheets to different codes (like PE0E1, 2 etc...) and then use those codes in the sheet & it will work - you are the man! :clap: