copy and check sheets in one action

[cocopops2001]

what i want to do is copy a sheet into a user workbook from a masterworkbook but to check if a cover sheet exists first.

here is a breakdown of the process.

user opens 'master workbook' and decides which sheet they want to copy (clicks copy button)

opens user workbook or creates new workbook(if this can be done in the same directory as the 'master' or even allow the user to decide which folder)

checks if the 'user workbook' has specific cover sheet(check if sheet1, cell A1 has "title" in it) if the cover sheet exist paste in desired sheet, if not create cover sheet then paste desired sheet. it is important that the cover sheet is put in sheet1 of the 'user workbook'

what i would like is a way for the macro to remember which sheet from the 'master' is to be copied. i am currently using

ActiveSheet.Range("a11:n70").Copy

the problem is that when the macro does the covercheck and has to copy/paste 'cover sheet' into new workbook the clipboard will clear and the desired sheet will not be copied.

thanks

 

[MrKowz]

Are you currently using a macro that (semi-)works? If so, can you please post the code? It might be easier to amend to your existing code.

 

[Norie]

If it's the active sheet you want to copy you could create a reference to it as soon as the button is clicked.

Set wsCopy = ActiveSheet

You can now use wsCopy in the rest of the code and it will always refer back to the worksheet that was active when the button is clicked.

 

[cocopops2001]

thanks norie the wscopy should work, when referring to it later on would i be able to use

wscopy.Range("xx:yy").Copy

???

 

[cocopops2001]

did not work heres how i have it, user pushes a button

Sub form()
    
    Set wscopy = ActiveSheet
     
    UserForm1.Show
    
End Sub

which opens a user form which allows them to create a new file or use an existing, the code for the user form is

Private Sub CommandButton2_Click()
    
        
        
    sheetcopy
    
End Sub

Private Sub UserForm_Click()

End Sub
Private Sub CommandButton1_Click()
        
    OpenCalc
    
   
    sheetcopy
    
End Sub

Private Sub CommonDialog1_AfterUpdate()

End Sub

Private Sub CommonDialog1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

Private Sub CommonDialog1_Enter()

End Sub

Private Sub CommonDialog1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

End Sub

the select file button runs this code

'code to choose the working spreadsheet in which to place calcs
'(Stewart Livingston 20/04/2011)
Sub OpenCalc()

Dim fn
fn = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls),*.xls", _
        Title:="Open File(s)", MultiSelect:=False)
If fn = False Then
MsgBox "Nothing Chosen, Create New Book"
UserForm2.Show
Else
MsgBox "You chose " & fn
Workbooks.Open fn

End If

End Sub

and the new file button runs

Sub sheetcopy()
    
    '''''checks if cover sheet exists and creates one if not (Stewart Livingston 21/04/2011)

    
    
    '''''gets the desired sheet from the MENU Workbook
    Dim wscopy As Object
    wscopy.Activate
    wscopy.Range("A11:N70").Copy
        
    Worksheets.Add After:=Worksheets(Worksheets.Count)
    Worksheets(Worksheets.Count).Range("a1").PasteSpecial
    Application.CutCopyMode = False
    
    
    ''updates links to new workbook
    ActiveWorkbook.ChangeLink Name:="intro.xls", NewName:=ActiveWorkbook.Name, Type:=xlExcelLinks
End Sub

as you can see in my sheetcopy module i want it to run a check whether a cover sheet exists and paste one if it doesnt(not wrote that bit of code yet)

 

[cocopops2001]

a bit of annoying thing also is the section of code that brings up userform2, can i change it to show userform1 again? i tried

 userform1.show

and got an error as the form was already on show but not at the front? i know what i want to happen and have got on quite well with programming just not done it very much. I have only got as far as i have throught searching forums and modifying code to suit my needs

 

[Norie]

What is it you want to do?

You said it was a sheet you want to copy but it only appears to be a range from a sheet.

Also what do the 2 userforms do?

It looks like most of the code you need is there but you aren't doing things in the right order, declaring/setting variables in the right places etc.

PS I suggested creating the reference to the active sheet in the button being clicked because I thought that was where all the code is.

Didn't realise userforms were involved.

 

[cocopops2001]

userform1 allows user to either select a book or create a new book where the desired sheet(range from the sheet) is to be copied. the reason i only want to selct the range is due to the fact that my master workbook will have a common menu bar along the top of everypage.


but as the copied sheet has links to the cover sheet(each page needs to have project title, who done the calc, who checked the calc, dates etc) in it a cover sheet has to be inserted into the workbook chosen by the user.

but by copying the cover sheet the calulcation sheet will not be in the clipboard anymore.

 

[Norie]

Sorry, I'm slightly more confused - you want to copy a cover sheet?

Or when you do that it causes problems?

Is this what you want to happen?

1 The user goes to a particular worksheet in the current workbook.

2 This worksheet is the one to copy from.

3 Then the user has the choice of creating a new workbook or chose an existing workbook.

4 If they want to create a new workbook then UserForm2 is displayed, not sure what happens next when that happens.

5 If they choose an existing workbook, that workbook is opened, a new worksheet is added to it and the data from the worksheet in step 1 is copied to it.

6 Also if the chosen workbook doesn't have the cover sheet workbook it's added and populated at needed.

7 Workbook saved and closed.

Anywhere close?

 

[cocopops2001]

thats pretty much exactly what i want it to do, userform2 only appears if they dont select a valid workbook or if they cancel the process. userform2 is a single button with create new workbook command