Help with creating Script for this

[phealey]

I have two separate spreadsheets.
Sheet1.xlsx (attached)
A is a location
B is Day
C is Hour
D is a count

second spreadsheet Data.xlsx also attached
Top Column starting with B is days 1 through 31.
Down the sides rows 2-17 are times 8am to 23pm

I want Dat.xlsx a Column B if it equals Sheet1.xlsx Column B and if Data.xlsx Column C = Sheet1.xlsx Column A then take the data from D and put it to appropriate location.

I am not good with vbs at all and could not find what i was asking for.
Sorry and thanks for any help.

 

[Marc L]

I am not good with vbs

Here it's a VBA forum, not a VBS one …​

​

If it's about VBA, as any of your files within your attachment can contain any VBA code​

so where the VBA procedure must be located ?​

And as your explanation is somewhat vague then attach at least the exact expected result …​

​

 

[phealey]

I am sorry i posted this in VBA but this is probably VBS i think. I am very new to this and wanting to make something easier if it can be done in a macro. I do not have a macro setup in the ones i posted because i honestly am not sure where to begin. I have attached the out come of what i was trying to do. I currently do this by hand and it is a lot.
The out come is from Data.xlsx The data in Column D gets inputted into the appropriate day and hour in Sheet1.xlsx. I appreciate your help and responding to my request. I am teaching myself some VBS so i can learn more.

 

[Marc L]

Macro does not exist anymore since last century so this is a generic term for a VBA code (thanks Microsoft for the confusion !)​

which can be stored within .xlsm & .xlsb workbooks.​

So, for a VBA procedure, you must mention in which workbook it must be located, maybe Sheet1,​

whatever but you must save it as binary format .xlsb as a .xlsx workbook can not store any VBA code …​

​

By the way is the Data workbook already opened when you launch the VBA procedure,​

is it saved in the same path than Sheet1 workbook, … ?​

Mention all any helper can't guess !​

​

To learn VBA use the 'Macro Recorder' and read VBA help, a good way to start.​

​

 

[phealey]

Thank you for the explanation and information on how to learn more. I want to learn more so i can be better at this. I was just thrown into this and was trying to figure out a better way to make my time doing this more efficient. Y

The Data workbook will already be opened and both excel files can be put in the same folder. The data sheet is just a download excel file so it put in the same path as Sheet1.xlsx. Sheet1 is where i would like the VBA code. If any more information is needed i will provide the best i can.

Thank you for assistance

 

[Marc L]

Just a doubt : should the VBA procedure overwrite the Sheet1 previous result or should add the new result to the previous one ?​

​

 

[phealey]

It can overwrite but it should not make a difference since the information being provided will be hours and days so it should never have to overwrite.

 

[Marc L]

Was according to a fast way which overwrites all the previous data​

so is the VBA procedure launched once a month or several times ?​

​

 

[phealey]

I should have included all this sorry for you having to ask all these questions. This will be done several times.

 

[Marc L]

According to your attachments a VBA demonstration for starters (best place is Sheet1.xlsb ThisWorkbook module) :​

Sub Demo1()
         Dim Wb As Workbook, D, H, V, W, R&, X, Y
    For Each Wb In Workbooks
          If Wb.Name = "Data.xlsx" Then Exit For
    Next
          If Wb Is Nothing Then Beep: Exit Sub
         D = Wb.Sheets(1).[A1].CurrentRegion
         Set Wb = Nothing
   With Sheets(1).[A1].CurrentRegion
        H = Application.Match("Avg", .Rows(1), 0)
        V = Application.Match("Total", .Columns(1), 0)
        If UBound(D, 2) < 4 Or IsError(H) Or IsError(V) Then Beep: Exit Sub
        H = .Cells(2).Resize(, H - 2)
        V = .Cells(2, 1).Resize(V - 2)
    With .Cells(2, 2).Resize(UBound(V), UBound(H, 2))
        W = .Value
    For R = 1 To UBound(D)
        X = Application.Match(D(R, 3), V, 0)
     If IsNumeric(X) Then
        Y = Application.Match(D(R, 2), H, 0)
        If IsNumeric(Y) Then W(X, Y) = D(R, 4)
     End If
    Next
       .Value = W
    End With
   End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[phealey]

I am gonna break all this code down for myself to better understand. This worked exactly as I wanted.
Thanks

 

[Marc L]

'All' is explained within VBA help except Match which is a worksheet formula function so within Excel help …​

​

To summarize : search first for a Data workbook opened : if not then beep, that's all folks !​

If found allocate its first sheet data to the array variable D.​

Then according to the result worksheet - aka Sheet1.xlsb first sheet - search for 'anchors' Avg & Total.​

If not found or Data columns # < 4 then beep, bye !​

When found allocate the days row to the array variable H and the hours column to the array variable V preceding their respective 'anchors'​

and allocate the actual result range to the array variable W.​

For each row of Data - Long variable R as row index - check the 'coordinates' within variable X & Y​

then if both found update their relative position in variable W.​

Finally write variable W back to the result range …​

​

 

[phealey]

it seems i have a problem. What you gave me does work but i did not mention that i have tabs at the bottom of months. Jan - Dec. I only showed you the one sheet. Once i apply the code you sent me it does not work i guess due to there are multiple sheets Jan -Dec. I did not know that would make a difference in how it worked. I have attached the entire look of the spreadsheet. I apologize that i did not give you all the information.

 

[phealey]

I can still make this work by just having only one tab in another sheet and then after it has run then copy that data over to the correct month. So what you have done for me works as it should but i did not provide all my information to you and that i am sorry. If you can make it work with the tab that is currently selected on Sheet1 that is what i would need it to do.

 

[Marc L]

As guessing can't be coding !​

​

Anyway just replace With Sheets(1).[A1].CurrentRegion with the codeline With ActiveSheet.[A1].CurrentRegion …​

​

 

[phealey]

you are correct i am not a coder at all i am trying to learn stuff but they have given me this task and i appreciate your help.
I did do the active sheet after i figure it out and then saw your message. Again thanks for your help. I hope to get much better at this and i will use the Help feature.

 

[Marc L]

So weird you have created a new thread on another Excel VBA forum …​

​

 

[phealey]

I did not want to request even more things from you since you had already done exactly as i wanted. To me I would have been being needy to you and asking you to do something else that was not requested to begin with. I apologize for not asking this forum first with what you had already helped me with. Thank you