Sleeper: Biffed something in the code...

[echane]

Hi, I was trying to copy information (set up in rows) from one sheet to other worksheets but it's not working correctly. I'm mainly trying to get it to know to copy information from each row into the next empty column on the other sheets but right now it's just copying the same row of information into the next 20 or so columns. I think I messed up some looping thing.

Also, if information there are two rows of information that should be copied to another worksheet, they should each have their own column. Asides from the repeating problem, it also just starts over with the first column and overwrites the first row's information. Please let me know if there's something really wrong with my code. Thanks!

Public Sub Populate()
Const InputCol As String = "A"
Dim i As Long
Dim j As Long
Dim LastRow As Long
Dim LastCol As Long
Dim sh As Worksheet
Set sh = Sheet4
With sh
    LastRow = .Cells(.Rows.Count, InputCol).End(xlUp).Row
    LastCol = ActiveSheet.Cells(4, ActiveSheet.Columns.Count).End(xlToLeft).Column
    For i = 7 To LastRow
        For j = 2 To LastCol
            .Cells(i, "A").Copy
            Sheets(.Cells(i, "A").Value).Select
            ActiveSheet.Cells(3, j).PasteSpecial Paste:=xlPasteValues
            .Cells(i, "B").Copy
            Sheets(.Cells(i, "A").Value).Select
            ActiveSheet.Cells(4, j).PasteSpecial Paste:=xlPasteValues
        Next j
    Next i
End With
End Sub

[mdmackillop]

Because i is not changing within this loop, the same info is being pasted repeatedly.

 
For j = 2 To LastCol 
  .Cells(i, "A").Copy 
    Sheets(.Cells(i, "A").Value).Select 
   ActiveSheet.Cells(3, j).PasteSpecial Paste:=xlPasteValues

This tidies your code a little (no change in functionality)

Public Sub Populate()
    Const InputCol As String = "A"
    Dim i As Long
    Dim j As Long
    Dim LastRow As Long
    Dim LastCol As Long
    Dim sh As Worksheet
    Dim ws As Worksheet
Set ws = ActiveSheet
    Set sh = Sheet4
    With sh
    LastRow = .Cells(.Rows.Count, InputCol).End(xlUp).Row
    LastCol = ws.Cells(4, ws.Columns.Count).End(xlToLeft).Column
    For i = 7 To LastRow
        For j = 2 To LastCol
            .Cells(i, "A").Copy
            Sheets(.Cells(i, "A").Value).Cells(3, j).PasteSpecial Paste:=xlPasteValues
            .Cells(i, "B").Copy
            Sheets(.Cells(i, "A").Value).Cells(4, j).PasteSpecial Paste:=xlPasteValues
        Next j
    Next i
    End With
End Sub

If you could post a small sample showing Data on Sheet 4, Confirm what is the ActiveSheet, and an example of what you are after, this should be easily sorted.

[echane]

Hi! Thanks for clearing that part up. I'm very new to this so I have a hard time structuring my code correctly. I've attached a sample of what I want it to look like. Basically, I'll have a bunch of worksheets set up already (ABC, XYZ in the sample) and anything that gets entered into the "Input" worksheet will automatically have that information pulled into the correct worksheet. Each item in the "Input" tab knows where to go....I just have that repeating and overwriting problem.

[echane]

I'm not trying to do something impossible am I? I'm assuming nothing's impossible with vba...

[mdmackillop]

I can't open your file. Can you save it as 2003 and repost it?

[geekgirlau]

Hi echane,

I think it would be worth taking a step back and looking at what you're trying to accomplish. Yes, what you've asking is possible with VBA, but I don't think it's necessarily the best solution.

Your sample is fairly limited, but I'm guessing that you want a data entry page and then a way to view the data for a specific group/company - is this correct?

What you might want is something like the following:

• A sheet that holds your data, probably hidden from the user.
• A sheet to display information by selecting the relevant Group and/or Company from a drop down list (using either a combo box control or in the cell using data validation). When you select your criteria (combination of group, company or both) the relevant data would be displayed.
• A sheet to record new information (add new groups/companies/questions).

We tend to think in terms of using lots of sheets (eg one sheet for each group) however it's not really necessary. You can have a much more streamlined workbook by having a single "reporting" sheet rather than lots of them.