Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a column in a worksheet from a formula worksheet based on the date
I need help, I don’t know what I am doing wrong. What I want to do is copy a
column (from a worksheet that contains formulas) and paste the data to another column on another worksheet (template with dates also) based on month and year. I want this to happen untill there is no more data for that month. Then next month I want it to take Feb 06 coulmn and do the same thing, and so on for the following month. I want to create this macro so that when a person clicks on a button that is linked to this macro…they update the template sheet with the current month’s data. I can’t simply copy the whole sheet that contains the formulas because I don’t want the previous months overwritten in the template. Below is an example of my data and what I have so far in VBA …and I know it is incomplete and perhaps not the right VBA codes I need…please help. Thank You in advance. Nadia moving left to right Column AD Inbound Nov-05 Dec-05 Jan-06 Feb-06 Oct-03 0 0 0 Nov-03 3 3 3 Dec-03 9 8 8 Jan-04 62 59 59 Feb-04 132 127 127 Mar-04 221 212 212 Apr-04 180 167 167 xrow = 5 xcolumn = AD Cxrow2 = 1 Range("AD5") = ("Jan-06") Range("AE5") = ("Feb-06") Range("AF5") = ("Mar-06") Do Until Range("xcolumn" & 6) = "" Sheets("Reporting month").Select If Range("L1") = "Jan-06" Then Range("AD6:AD44").Select Selection.Copy Sheets("Data").Select Range("AD6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False If Range("AE" & xrow) = "Feb-06" Then Range("AE6:AE44").Select Selection.Copy Sheets("Data").Select Range("AE6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If If Range("AF" & xrow) = "Mar-06" Then Range("AF6:AF44").Select Selection.Copy Sheets("Data").Select Range("AF6").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False xcolumn = xcolumn + 1 Exit Do End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a column in a worksheet from a formula worksheet based on the date
Hi Don,
do I apply what's below to my current VB? or do I delete everything I have and use yours? thanks so much Don Guillett wrote: This should work for the current month from anywhere in the workbook. It will not work for March if you wait until April but you can change the mm variable. Sub findfirstdate() Set frng = Sheets("sourcesheet").Columns(1) Set trng = Sheets("destinationsheet").Columns(1) my = Year(Date) mm = Month(Date) fr = Application.Match(CDbl(DateSerial(my, mm, 1)), frng, 1) + 1 lr = Application.Match(CDbl(DateSerial(my, mm + 1, 1)), frng, 1) - 1 dr = Application.Match(CDbl(DateSerial(my, mm - 1, 31)), trng, 1) + 1 Sheets("sourcesheet").Rows(fr & ":" & lr). _ Copy Sheets("destinationsheet").Rows(dr) End Sub I need help, I don’t know what I am doing wrong. What I want to do is copy a [quoted text clipped - 73 lines] End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a column in a worksheet from a formula worksheet based on the date
I tried this but it did not work, I need it to start at January...any
suggestions? NadiaR wrote: Hi Don, do I apply what's below to my current VB? or do I delete everything I have and use yours? thanks so much This should work for the current month from anywhere in the workbook. It will not work for March if you wait until April but you can change the mm [quoted text clipped - 17 lines] End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a column in a worksheet from a formula worksheet based on the date
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for specific dates based on another date in worksheet | Excel Discussion (Misc queries) | |||
How can I create a fill that increments based on worksheet numbers | Excel Worksheet Functions | |||
Update a formula based on date worksheet was saved | Excel Worksheet Functions | |||
How to create a fill down that increments based on the worksheet n | Excel Worksheet Functions | |||
Fill automatically from one worksheet to another based on cel valu | Excel Discussion (Misc queries) |