Could anyone please help me with this!??
I'm currently using excel 2003 and need to write a macro to do the following but i can't figure out how to do it!
I have a main worksheet in a workbook thats called MonthData and several other worksheets within the workbook for each month. Each worksheet is called after its month and year, eg: Jan 09.
What i want to do is create a macro where i could type the month and year i want to access in a cell, b2, in the spreadsheet MonthData, and then whatever month and year i would type in, it would search for the corresponding month and year in the workbook and then copy the cells b17:h49 from that worksheet and then paste it into cells b14:h46 of the worksheet MonthData.
Any help would be greatly appreciated!!!
Thankyou!!!
Hi
You don't need a macro to do this - you can use formulas.
In MonthData!B14 enter the formula
Copy down / across as required.=OFFSET(INDIRECT($B$2&"!a1"),ROW()+2,COLUMN()-1)
Now when you enter your sheet name in B2, it will bring back the data from that sheet.
HTH
rylo
Hi excelsi,
Welcome to the forum.
If you really need a macro, you could try this:
HTHCode:Sub Macro1() 'If there is no sheet called by the value in _ cell A2 (change if required) of the activesheet, then... On Error Resume Next If IsError(Len(ThisWorkbook.Sheets(Range("A2").Value).Name)) = True Then '...inform the user so and quit the routinue. MsgBox "This is no tab called " & Range("A2").Value & " in this workbook.", vbExclamation, "Data Consolidation Editor" Exit Sub 'Else... Else 'Copy the values from the desired tab and range to the active sheet. Sheets(Range("A2").Value).Range("B17:H49").Copy _ Range("B17") End If End Sub
Robert
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to add to our reputation by clicking the blue scale icon in the top right-hand corner of my post
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks