Link to home
Start Free TrialLog in
Avatar of Manju
ManjuFlag for India

asked on

Excel Macro - Loop Help

Experts - I have an excel workbook with 3 sheets.

"Calendar"
"Resources"
"Final"

"Calendar" - This sheet will have current month dates ("Date, Day, Month, Week, Qtr etc") Ex: Current month will have 1st Jan to 31st Jan data. Similarly every new month, data here gets refreshed.
"Resources" - This will have EmpID & Name - Constant data

What I need is...,
in "Final" sheet, For Each date in the calendar, list all resources details
Ex:
Calendar has
1/1/18
1/2/18
....
1/31/18

Resources has
123 ABC
122 CCC
111 BBB

Final Should have
1/1/18 123 ABC
1/1/18 122 CCC
1/1/18 111 BBB
....
and so on till 31st Jan.

Kindly help me with a macro
Avatar of Norie
Norie

How do the dates on 'Calendar' tie in with the data on 'Resources'?
Avatar of Manju

ASKER

It doesnt tie at all.

Calendar is independent of resources.
So how can it be determined what data goes where on the 'Final' sheet?
Avatar of Manju

ASKER

I mean, lets say Calendar has 1 to 31 dates in rows
and Resources are 3 rows

Final should be 3 * 31
For each date, 3 resources & so on.
Without relationship between ressources and dates, there isn't much we can do, beside using the CONCATENATE function.
Manju

Perhaps some sample before/after data might help clarify things, could you attach a sample workbook?
I think it's pretty simple
Here's an example
Sub Final()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim r As Range
    Dim r1 As Range
    Dim i1, i2, ifinal As Integer
    
    Set ws1 = ThisWorkbook.Worksheets("Calendar")
    Set ws2 = ThisWorkbook.Worksheets("Resources")
    Set ws3 = ThisWorkbook.Worksheets("Final")
  
    For Each r In ws1.UsedRange.Rows
        i1 = i1 + 1
        i2 = 0
        For Each r1 In ws2.UsedRange.Rows
            i2 = i2 + 1
            ifinal = ifinal + 1
            ws1.Range("A" & i1).Copy ws3.Range("A" & ifinal)
            ws2.Range("A" & i2).Copy ws3.Range("B" & ifinal)
            ws2.Range("B" & i2).Copy ws3.Range("C" & ifinal)
        Next
    Next

End Sub

Open in new window

Just to see how it works here's the excel file with the macro in example
ExEx.xlsm
Avatar of Manju

ASKER

@Ferruccio Accalai - Pretty near. I've attached a sample file. Your query is behaving erratic with different columns being pulled. Can you check the attachment & modify your code please?
Test_loop.xlsm
ASKER CERTIFIED SOLUTION
Avatar of Ferruccio Accalai
Ferruccio Accalai
Flag of Italy image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Manju

ASKER

Perfect