Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
I am trying to take information that is on a worksheet who's name
stays constant that gets updated daily from another one and then paste that information into another worksheet. The layout would be constantworksheet(data): A1 B1 C1 D1 Name Jobtype1 Jobtype2 Jobtype3 Jdoe 5 21 6 Rdoe 8 18 7 and paste into a monthly worksheet A1 B1 C1 D1 Name Day1 Day2 Day3 .... Jdoe Jobtype1 5 Jobtype2 21 Jobtype3 6 Totals sum Rdoe Jobtype1 5 Jobtype2 18 Jobtype3 7 how would I get a macro automatically pull over the information? Would I do a lookup on the name of the activesheet to the datasheet, select which column i want, then use offset 0,1 to move down a column and use an input box to select which day(column) they want to paste information in for each person? Any direction would be appreciated! -Marcus |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
It is unclear exactly what you want, but perhaps there is something here you
can use. dim r as Range, r1 as Range On error resume Next set r = Application.InputBox("Select cell to paste to with mouse", type:=8) set r1 = Application.InputBox("Select cells to copy with mouse", type:=8) On error goto 0 if r is nothing or r1 is nothing then exit sub r1.copy r.PasteSpecial xlPasteAll, transpose = True For completeness There is a bug in the use of this function if your sheet contains conditional formatting using the Formula Is dropdown: http://www.jkp-ads.com/Articles/SelectARange.asp Hopefully that won't affect you. -- Regards, Tom Ogilvy "Marcusdmc" wrote: I am trying to take information that is on a worksheet who's name stays constant that gets updated daily from another one and then paste that information into another worksheet. The layout would be constantworksheet(data): A1 B1 C1 D1 Name Jobtype1 Jobtype2 Jobtype3 Jdoe 5 21 6 Rdoe 8 18 7 and paste into a monthly worksheet A1 B1 C1 D1 Name Day1 Day2 Day3 .... Jdoe Jobtype1 5 Jobtype2 21 Jobtype3 6 Totals sum Rdoe Jobtype1 5 Jobtype2 18 Jobtype3 7 how would I get a macro automatically pull over the information? Would I do a lookup on the name of the activesheet to the datasheet, select which column i want, then use offset 0,1 to move down a column and use an input box to select which day(column) they want to paste information in for each person? Any direction would be appreciated! -Marcus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
Thank you for the response! That would work for sure, but I'm looking
for a more automated way to copy type 1 on datasheet which is arranged vertically and paste in job type 1 on monthsheet which is arranged horizontally. -Marcus |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
The input box would be to determine which date(column) you wanted to
paste into for each person on the monthly sheet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
Here is some untested pseudo code:
Dim r as Range, r1 as Range Dim r2 as Range, cell as Range Dim res as Variant On error resume Next set r = Nothing set r = Application.InputBox( _ "Select destination column in Summary",type:=8) On Error goto 0 if r is nothing then exit sub with worksheets("Daily") set r1 = .Range(.Cells(2,1),.Cells(2,1).end(xldown)) end with with worksheets("Summary") set r2 = .Range(.Cells(2,1),.Cells(2,1).End(xldown)) End with for each cell in r1 res = Application.Match(cell,r2,0) if not iserror(res) then set r3 = r2(res) cell.offset(0,1).Resize(1,3).copy r3.offset(1,r.column-1).Pastespecial Transpose:=True end if Next -- Regards, Tom Ogilvy "Marcusdmc" wrote: The input box would be to determine which date(column) you wanted to paste into for each person on the monthly sheet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
Trying to make it so that instead of naming the worksheet "summary",
they can just be on the worksheet they want to modify with the information from the "daily" page. trying this but it's not working, getting a type mismatch on the with works Sub TestMe() Dim mSheet As Worksheet Set mSheet = ActiveSheet Dim sr As Range, sr1 As Range Dim sr2 As Range, cell As Range Dim res As Variant On Error Resume Next Set sr = Nothing Set sr = Application.InputBox( _ "Select Weekly Column to update", Type:=8) On Error GoTo 0 If sr Is Nothing Then Exit Sub With Worksheets(mSheet) Set sr1 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With With Worksheets(mSheet) Set sr2 = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With For Each cell In sr1 res = Application.Match(cell, r2, 0) If Not IsError(res) Then Set sr3 = sr2(res) cell.Offset(0, 1).Resize(1, 3).Copy r3.Offset(1, r.Column - 1).PasteSpecial Transpose:=True End If Next End Sub -Marcus |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro with input box to Select a column on active sheet
Actually I figured out the answer!!! :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set printer default to select active sheet not whole book | Excel Discussion (Misc queries) | |||
How to select the active sheet? | Excel Worksheet Functions | |||
Copy my active sheet to a new sheet and open with an input form | Excel Programming | |||
how do I select cells in column A thru F in the active row? | Excel Programming | |||
Automatically Display a data input form when active sheet changes | Excel Programming |