VBA Guru's?
19 November 2009, 10:02 AM
#1
VBA Guru's?
I am just getting into vba (in a sad but non sexual way) and have a particular problem that I am struggling with & no amount of searching on google seems to help that I can find. It should be relatively simple but!
We record daily stats from a variety of sources/reports then enter them into a spreadsheet we use for management overview. Have got macros running to produce all the data and "bring" it to the overview spreadsheet in a fixed column. However, I need to put it into the column for yesterday's data or indeed the relevant day's data.
For instance, yesterday's column might be YA and the rows would be 13, 15, 16 & 20. The data is brought (currently) into A13, A15, A15 & A20. I need to copy it across based on yesterday's date and not into a column that is tomorrow's date or 2 weeks ago's date. YA2 would have yesterday's date already prepopulated so I can use as a reference & obvioulsy the data brought in can have a date within.
Any ideas?
We record daily stats from a variety of sources/reports then enter them into a spreadsheet we use for management overview. Have got macros running to produce all the data and "bring" it to the overview spreadsheet in a fixed column. However, I need to put it into the column for yesterday's data or indeed the relevant day's data.
For instance, yesterday's column might be YA and the rows would be 13, 15, 16 & 20. The data is brought (currently) into A13, A15, A15 & A20. I need to copy it across based on yesterday's date and not into a column that is tomorrow's date or 2 weeks ago's date. YA2 would have yesterday's date already prepopulated so I can use as a reference & obvioulsy the data brought in can have a date within.
Any ideas?
19 November 2009, 03:12 PM
#2
Scooby Regular
Join Date: Jan 2006
Posts: 897
Likes: 0
Received 0 Likes
on
0 Posts
Copying and pasting from Column A seems like a double step, can you not just Paste / Enter it directly into the required column?
You could get the required column no either by Searching for Yesterdays Date
or
By Using the last available column (Assumes Row 1 is always populated)
Use the defined column number to copy & paste or enter data directly into the cell
NOTE: Range("IV1") is based on Excel 2003, I'm assuming you're using 2007 - Replace this with the last column in a worksheet for the same effect.
You could get the required column no either by Searching for Yesterdays Date
Code:
Dim icol As Single icol = ActiveSheet.Cells.Find(What:=Date - 1, _ SearchDirection:=xlNext, _ SearchOrder:=xlByRows).Column
By Using the last available column (Assumes Row 1 is always populated)
Code:
icol = Range("IV1").End(xlToLeft).Column + 1
Code:
Cells(13, 1).Copy Cells(13, icol).PasteSpecial Paste:=xlPasteValues
20 November 2009, 07:02 PM
#5
Eventually did this:
icol = .Match(workbook.Sheets("Sheet1").Cells(3, 2), Workbooks("filename.xlsm").Sheets("sheet1").Range( "2:2"))
Which looked at the source data (which includes dates) and matched the date there against the appropriate column. After that used the icol variable to copy the data straight in.
So many thanks - enjoying this stuff
20 November 2009, 10:48 PM
#6
Scooby Regular
Join Date: Jan 2006
Posts: 897
Likes: 0
Received 0 Likes
on
0 Posts
No Problem
mrexcel.com is a very good resource.
The message boards have plenty of experts that can help you do things with VBA that you wouldn't think possible - Powerful stuff
mrexcel.com is a very good resource.
The message boards have plenty of experts that can help you do things with VBA that you wouldn't think possible - Powerful stuff
Trending Topics
Thread
Thread Starter
Forum
Replies
Last Post
Mick
Computer & Technology Related
3
22 May 2003 01:24 PM
