Help with macro

[jw191]

Hi Guys,

Got a bit of a problem.....

I have a financial excel workbook with several sheets in it. One of the sheets is a monthly budget template which the end user will have to duplicate to cover different different projects. There is a summary sheet which gathers data from these monthly budget worksheets and displays it accross a aingle row - so if the workbook has 5 project sheets, the summary sheet has five lines of data.

I have been trying to write/record a macro which performs the following:

1) Duplicate template sheet
2) Copies data from a range of cells (one row) on the newly created sheet
3) Prints the data to the next free row in the summary sheet.

Any hints would be greatly appreciated.

[Carim]

Hi,

Why don't you upload a zipped copy of your worksheet, for a precise answer ...

HTH
Carim

[jw191]

Hi,

actually made a bit more progress on this, only one thing left I'm stuck with. Is there a VB command to move down one row without having to use variables?

[Carim]

Yes ...

Please Login or Register  to view this content.

HTH
Carim

[jw191]

Yes ...

Please Login or Register  to view this content.

HTH
Carim

Thanks a lot for that Carim, been trauling webpages for hours looking for that command.

[Carim]

Glad I could help you out ...

Thanks for the feedback

Carim

[jw191]

Getting sucked into this whole VBA for excel thing and keep thinking of enhancements I can make. A new development I have included is to take input from the user to name a new worksheet that I create - This I have been able to do, the problem is (as I predicted) that if the user tries to name a worksheet the same as a current worksheet an error occurs. Code Below:

Sub CreateBoughtIn()
'
' CreateBoughtIn Macro
' Macro written 20/12/2006 by James Wilkinson
'
' Keyboard Shortcut: Ctrl+Shift+B
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim strName As String
strName = InputBox(Prompt:="Please Enter Task Number - this will form the name of the worksheet created", _
Title:="ENTER TASK NUMBER", Default:="")
If Not strName = ("") Then
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Copying links from new sheet into summary sheet
Sheets("FCast Summary").Select
Range("B6").Select
Selection.End(xlDown).Select
If ActiveCell = ("") Then
Range("B7").Select
Else
ActiveCell.Offset(1, 0).Select
End If
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Bought-In Template").Select
Sheets("Bought-In Template").Copy Before:=Sheets(11)
ActiveSheet.Tab.ColorIndex = 2
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''rename new sheet
ActiveSheet.Name = strName
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Range("B111:R111").Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("FCast Summary").Select
ActiveSheet.Paste Link:=True
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sheets(strName).Select
Range("A8").Select
Range("B1").Select
Else
MsgBox ("No Data Entered - Creation Aborted")
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

What I need is a way of searching the names of current worksheets to make sure the new sheet name entered has not allready been taken. Is this possible?