PDA

View Full Version : Solved: Populating fields



austenr
11-28-2006, 02:19 PM
Can someone suggest the best way to populate the fields on sheet1 page 1 with the data on page 2 besides hard coding them? My problem is that each month the columns will be moved over 1 column and new data added to page 2. Basically it is a rolling 13 month report with the oldest month being dropped off. Workbook attached. Thanks

mdmackillop
11-28-2006, 04:03 PM
Why not a simple copy? I'm not sure what you mean by "hard coding" here.

Sub CopyData()
Sheets(2).Range("B1:M129").Copy Sheets(1).Range("B46")
End Sub

austenr
11-28-2006, 05:05 PM
I suppose that would work. I guess if you work on something a while you start to second guess yourself about things and the easiest way is not the most obvious. :bug: I will give it a try that way.

Bob Phillips
11-28-2006, 05:08 PM
It seems to be added at the start of the data on Sheet2, and same on Sheet1, so hard-coding it seems fine. Just chop it off on Sheet1.

austenr
11-28-2006, 06:33 PM
Malcombs suggestion seems to work fine for me. One more thing, is it possible to populate the sheet based on-click of the selection in the drop down? Would I need to put everything in a worksheet change event?

austenr
11-28-2006, 07:35 PM
I knew I could not do a straight copy and paste for a reason. If you notice, the destination of the data has blank rows and there are a couple of fields that are calculated based on data in other fields in the column. So I guess a strainght copy/paste will indeed not work. Back to the original problem. :banghead:

mdmackillop
11-29-2006, 01:23 AM
We're lacking information to assist here Austen.

Aussiebear
11-29-2006, 03:04 AM
Hey MD, is this a similar thing that that which I had when I wanted the latest test result to be recorded and if there was already three columns of data the left most one was deleted and the next two columns of data were booted one column left, to enable the new data to be entered?

Ted

mdmackillop
11-29-2006, 04:38 AM
Hi Ted,
Similar as I recall, but here we've got to get some unknown formulae to function.

austenr
11-29-2006, 07:09 AM
Malcomb,

Say I want the unpopulated cells to populate when the value of B1 changes. So for example, in cell B1 I choose CT. I want to populate all the data with what is below the unfilled data. I can do the appropriate copies but I need to know if I should use a Select Case in a Worksheet Change Event or what approach to take. Since I always drop off the last column each month and add a new one at the front the months will always stay the same, so, if I code the copy and paste once and have a way to select the correct state I should be set. Im trying to avoid many subs and calling them which isnt a good idea. HTH

austenr
11-29-2006, 08:01 AM
OK I have this in a Worksheet Change Event but nothing gets copied. What am I missing?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("B1").Value = "CT" Then
Sheets("Summary").Range("H3").Value = "Connecticut All Markets"
Sheets("Totals For SummarySheet").Range("B3:N3").Copy Sheets("Summary").Range("E9")
Sheets("Totals For SummarySheet").Range("B4:N4").Copy Sheets("Summary").Range("E10")
Sheets("Totals For SummarySheet").Range("B5:N5").Copy Sheets("Summary").Range("E13")
Sheets("Totals For SummarySheet").Range("B6:N6").Copy Sheets("Summary").Range("E15")
Sheets("Totals For SummarySheet").Range("B7:N7").Copy Sheets("Summary").Range("E16")
Sheets("Totals For SummarySheet").Range("B9:N9").Copy Sheets("Summary").Range("E18")
Sheets("Totals For SummarySheet").Range("B10:N10").Copy Sheets("Summary").Range("E22")
Sheets("Totals For SummarySheet").Range("B12:N12").Copy Sheets("Summary").Range("E24")
End If
If Range("B1").Value = "DE" Then
Sheets("Summary").Range("H3").Value = "Deleware All Markets"
Sheets("Totals For SummarySheet").Range("B16:N16").Copy Sheets("Summary").Range("E9")
Sheets("Totals For SummarySheet").Range("B17:N17").Copy Sheets("Summary").Range("E10")
Sheets("Totals For SummarySheet").Range("B18:N18").Copy Sheets("Summary").Range("E13")
Sheets("Totals For SummarySheet").Range("B19:N19").Copy Sheets("Summary").Range("E15")
Sheets("Totals For SummarySheet").Range("B20:N20").Copy Sheets("Summary").Range("E16")
Sheets("Totals For SummarySheet").Range("B22:N22").Copy Sheets("Summary").Range("E18")
Sheets("Totals For SummarySheet").Range("B23:N23").Copy Sheets("Summary").Range("E22")
Sheets("Totals For SummarySheet").Range("B25:N25").Copy Sheets("Summary").Range("E24")
End If
If Range("B1").Value = "IL" Then
Sheets("Summary").Range("H3").Value = "Illinois All Markets"
Sheets("Totals For SummarySheet").Range("B29:N29").Copy Sheets("Summary").Range("E9")
Sheets("Totals For SummarySheet").Range("B30:N30").Copy Sheets("Summary").Range("E10")
Sheets("Totals For SummarySheet").Range("B31:N31").Copy Sheets("Summary").Range("E13")
Sheets("Totals For SummarySheet").Range("B32:N32").Copy Sheets("Summary").Range("E15")
Sheets("Totals For SummarySheet").Range("B33:N33").Copy Sheets("Summary").Range("E16")
Sheets("Totals For SummarySheet").Range("B35:N35").Copy Sheets("Summary").Range("E18")
Sheets("Totals For SummarySheet").Range("B36:N36").Copy Sheets("Summary").Range("E22")
Sheets("Totals For SummarySheet").Range("B38:N38").Copy Sheets("Summary").Range("E24")
End If
End Sub

SamT
11-29-2006, 10:01 AM
Try

Sheets("Totals For SummarySheet").Range("B3:N12").Copy _
destination:=Sheets("Summary").Range("E9")




For your consideration, I think this example is easier to maintain


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

ActiveWorkbook.Names.Add _
Name:="ReportName", _
RefersTo:="=Sheets("Summary")!$H$3

ActiveWorkbook.Names.Add _
Name:="ConnecticutZone", _
RefersTo:="=Sheets("Totals For SummarySheet")!$B$3:$N$12

ActiveWorkbook.Names.Add _
Name:="DelewareZone", _
RefersTo:="=Sheets("Totals For SummarySheet")!$B$16:$N$25

ActiveWorkbook.Names.Add _
Name:="IllinoisZone", _
RefersTo:="=Sheets("Totals For SummarySheet")!$B$29:$N$38

ActiveWorkbook.Names.Add _
Name:="SummaryZone", _
RefersTo:="=Sheets("Summary")!$E$9


Select Case Range("B1").Value
Case "CT"
ReportName.Value = "Connecticut All Markets"
ConnecticutZone.Copy destination:=SummaryZone
Case "DE"
ReportName.Value = "Deleware All Markets"
DelewareZone.Copy destination:=SummaryZone
Case "IL"
ReportName.Value = "Illinois All Markets"
IllinoisZone.Copy destination:=SummaryZone
Case Else
'Error Handling
Exit Sub
End Select

'Cleanup
ActiveWorkbook.Names("ReportName").Delete
ActiveWorkbook.Names("ConnecticutZone").Delete
ActiveWorkbook.Names("DelewareZone").Delete
ActiveWorkbook.Names("IllinoisZone").Delete
ActiveWorkbook.Names("SummaryZone").Delete

End Sub



Now I just hope that example works.

SamT,
Just a noob trying hard.

austenr
11-29-2006, 11:38 AM
will let you know. thanks a lot

mdmackillop
11-29-2006, 12:37 PM
I think you'll need to refer to the range differently ie
Range("ConnecticutZone").Copy destination:=Range("SummaryZone")

SamT
11-30-2006, 09:37 AM
Mack,

I Read Johns' Evaluate Article after the above post. Do you think

[ConnecticutZone].Copy destination:=[SummaryZone ]


Would work?

austenr
11-30-2006, 09:47 AM
Ok I have worked on this the better part of the morning. What I decided to do was to put the data (which isn't hidden at the moment) under the cells to be populated. Please check the code and see if it looks reasonable. There is a problem though, I have two cells on the sheet to make a selection from. Either a state or a market. How can the code differentiate between the two? Currently if you open the workbook and select either cell B1 or B3 it stays in a loop and nothing happens. Is there a way around this or do I need to use two sheets which I did not want to do. Sorry this is so difficult to explain.

austenr
11-30-2006, 12:39 PM
Managed to solve my problem. Not the way I intended but it works. Thanks to all.