I am fairly new to VBA and struggling to understand why my macro is not working.
The context: I have an Excel workbook with 3 sheets:
- Sheet"Department" with a list of names
- Sheet"Accounts" with a list of accounts
- Sheet"Departments and Accounts" which is where my output should be
What I am trying to do I want to :
- Copy the list of all the accounts from the Sheet "Accounts" and paste it on the Sheet "Departments and Accounts"in column A
- go to the Sheet "Department", copy the first department name
- go to the sheet "Departments and Accounts" and paste the department name in column B until next to the account
- repeat until all my department have been pasted
Desired output I would have the bloc with all the accounts with one department next to it, and as many blocs as there are departments on the list. On the sheet it would look like this in the picture I attached.
My code so far:
Sub Macro1() ' ' Macro1 Macro ' Dim lrow As Long Dim i As Integer lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To 47 Sheets("Accounts").Select Range("A2:A178").Select Selection.Copy Sheets("Account and Dpt").Select Range("A" & lrow + 1).Select ActiveSheet.Paste Sheets("Departments").Select ' Range("B" & i + 1).Select Application.CutCopyMode = False Selection.Copy Sheets("Account and Dpt").Select Range("B" & lrow + 1).Select Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste i = i + 1 Next i End Sub
It is simply not working at all - it just pastes the accounts but that's it, and I don't understand why. Could you please help me fixing it ?