About Us

Rowan Drummond[_3_]

Ryan

I'm glad you found a solution.

Regards
Rowan

Agasnine wrote:
This is how the page is layed out.
the input sheet has:
A date entered into A2
Data entered into a range of cells (B2:C20)
All of the data entered into cells B2:C20 is related to the date
entered into A2.
At the end of the week the data and date are changed to the new data
The Main sheet has:
A series of weekending dates (for the entire year) all on row 4
The data from teh input sheet (B2:C20) needs to be pasted one cell
down and two
cells left. (FYI the reason they are pasted there is because
those cells are
actually hidden because they are just used in a formula to get
a percentage
that is in the cells directly below the date.

I found a macro that worked, I just wanted to make it clear what I was
looking for. Your macro would have worked if I was looking to take one cell
and move it to the Main page. Unfortunatly I was looking to move all of the
cells in B2:C20.

This is the macro that ended up working.

Sub CopyDataBlock()
Dim dt As Date, res As Variant
Dim rng As Range
'Take a specified date from cell A2 on page "Input"
dt = Sheets("Input").Range("A2").Value
' find that date on page "Main".
' all dates on "Reports" are in row 4.
res = Application.Match(CLng(dt), _
Sheets("Main").Rows(4), 0)
If Not IsError(res) Then
'copy B2:C20 to a cell range that
'starts Left 2, Down 1
Set rng = Sheets("Main").Cells(5, res - 2)
Sheets("Input").Range("B6:C20").Copy Destination:=rng
End If
End Sub

Thanks for the help with this macro. I had been racking my brain for hours
on end trying to figure out how to do this. Your promt responce (24 minutes)
to my first post put faith in this discussion group which caused me to
continue to use this. Thanks for all the help.
-Ryan

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I find "Stop Recording toolbar" of macros?	Eitan	Excel Discussion (Misc queries)	1	August 19th 06 12:24 PM
HELP on "left","right","find","len","substitute" functions	serene83	Excel Discussion (Misc queries)	5	June 27th 06 02:23 AM
adding reference "can't find project or library"	yule1111	Excel Programming	1	July 18th 04 03:22 PM
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc	Bob Reynolds[_2_]	Excel Programming	0	March 4th 04 08:52 PM

Menu

About Us