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:
Code
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
Display More
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 ?
Thank you!!