VB Help

[chrisscline]

I have a workbook with two sheets, Primary and Software. The sheets have the columns that I would like to copy to a third sheet, "Users_Software":

Primary

• Machine Name
• User

Software

• Machine Name
• Software

On the Primary sheet, the values in Machine Name and User are unique:
Dell 1 | Mary
Dell 2 | John

On the Software sheet, however, the Machine Name column will list the machine name for all software packages installed:
Dell 1 | Adobe Acrobat
Dell 1 | Microsoft Office
Dell 1 | Firefox

The end goal is that I want to build a pivot table based on the data copied to "Users_Software" so the data in this sheet would need to look something like
Dell 1 | Mary | Adobe Acrobat
Dell 1 | Mary | Microsoft Office
Dell 1 | Mary | Firefox
Dell 2 | John | Adobe Acrobat
Dell 2 | John | Microsoft Office
Dell 2 | John | Internet Explorer

I have the data copy code to where it will copy the data but not in the format above. It looks something like:
Dell 1 | Mary | Dell 1 | Adobe Acrobat
Dell 2 | John | Dell 1 | Microsoft Office
Dell 3 | Bert | Dell 1 | Firefox

Any help on this is much appreciated!

 

[b.downey]

Can you provide the code you have?

 

[b.downey]

Here is the code:

Option Explicit
Sub Process()
    Dim Wb As Workbook
    Dim trg As Single
    Dim PriRowNo As Long
    Dim SwRowNo As Long
    Dim DstRowNo As Long
    
    Dim WsPri As Worksheet
    Dim WsSw As Worksheet
    Dim WsDst As Worksheet
    
    Dim a As String
    Dim b As String
    
    Set Wb = ThisWorkbook
    Set WsPri = Wb.Worksheets("Primary")
    Set WsSw = Wb.Worksheets("Software")
    Set WsDst = Wb.Worksheets("Users_Software")
    
    DstRowNo = 1
    For PriRowNo = 1 To WsPri.Cells(WsPri.Rows.Count, "A").End(xlUp).Row
        a = Trim(WsPri.Cells(PriRowNo, "A"))
        b = Trim(WsPri.Cells(PriRowNo, "B"))
        
        For SwRowNo = 1 To WsSw.Cells(WsSw.Rows.Count, "A").End(xlUp).Row
            If a = Trim(WsSw.Cells(PriRowNo, "A")) Then
                WsDst.Cells(DstRowNo, "A") = a
                WsDst.Cells(DstRowNo, "B") = b
                WsDst.Cells(DstRowNo, "C") = WsSw.Cells(SwRowNo, "b")
                DstRowNo = DstRowNo + 1
            End If
        Next SwRowNo
    Next PriRowNo
End Sub

 

[chrisscline]

Thanks for the help! I apologize for the slow response... in-laws coming into town this week so I was busy getting ready for that.

The original set of code I have is below. It's very basic and I know I should be using variables but this was working very well. Also, I tried your code and it appears to have counted the number of rows on the Software sheet with Dell 1 in the cell (40 occurrences) and then copied that set of rows 40 times to the Users_Software sheet.

Original code:
Sub Copy_Data()

'Create New Worksheet
Worksheets.Add().Name = "Users_Software"

'Copy Data from "Primary" sheet and paste to "Users_Software"
Sheets("Primary").Range("$A").Copy _
Destination:=Sheets("Users_Software").Range("$A")
Sheets("Primary").Range("$K:").Copy _
Destination:=Sheets("Users_Software").Range("$B")

'Copy Data from "Software" sheet and paste to "Users_Software"
Sheets("Software").Range("$A:$B").Copy _
Destination:=Sheets("Users_Software").Range("$C:$D")
Sheets("Software").Range("$D:$D").Copy _
Destination:=Sheets("Users_Software").Range("$E:$E")




End Sub

 

[chrisscline]

As an update to my previous response, I now see what the code is doing. It is copying the software column correctly but is using Dell 1 and the user column for all rows.

 

[chrisscline]

With some slight modifications, I got this to work.

I ended up modifying:

If a = (WsSw.Cells(PriRowNo, "A")) -to-
If a = (WsSw.Cells(SwRowNo, "A"))

and

WsDst.Cells(DstRowNo, "A") = a -to-
WsDst.Cells(DstRowNo, "A") = WsSw.Cells(SwRowNo, "a")

Thanks for the assistance in pointing me in the right direction.

 

[b.downey]

No problem. Thanks for the reply