PDA

View Full Version : Help! Biffed something in the code...



echane
11-23-2009, 02:00 PM
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
11-23-2009, 03:14 PM
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
11-23-2009, 04:22 PM
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
11-24-2009, 10:30 AM
I'm not trying to do something impossible am I? I'm assuming nothing's impossible with vba...

mdmackillop
11-24-2009, 04:38 PM
I can't open your file. Can you save it as 2003 and repost it?

geekgirlau
11-24-2009, 10:02 PM
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.