Macro to move sheets

[jag108]

I need code that will identify seperate sheets, copy one to the other. Using variable assignment, I need to set variable to the sheets, then to the worksheet that it will be saved as. I have tried this one before but still having trouble getting it to work.

sheet-a + sheet-b moved then saved as workbook-1 or sheet-b moved to sheet-a then saved it is not a real problem. the real problem is if I have other workbooks open.
This message was edited by jag108 on 2002-10-08 19:11
This message was edited by jag108 on 2002-10-08 19:12

 

[Andrew Poulsom]

If you post your code I am sure someone will be able to show you how to overcome your problem.

In the meantime, VBA has some useful Properties that may be relevant in your case:

ThisWorkbook - Returns a Workbook object that represents the workbook where the current macro code is running.

ActiveWorkbook - Returns a Workbook object that represents the workbook in the active window (the window on top).

ActiveSheet - Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook.

So in your code you can precede sheet references with ThisWorkbook and always be sure that VBA will use the sheet in the workbook where the code is running.

If you copy a sheet from one workbook to another then the target workbook becomes the ActiveWorkbook and the new sheet becomes the ActiveSheet. But the source workbook will still be ThisWorkbook if that is where the code is.

 

[jag108]

Your reply gave me an Idea, it also reminded me of what was happening during my processing, which prompted me to use this approach (which works fine)

Sub copysheet()
Dim dest_file, final_file, Open_wkbk_sheet

Open_wkbk = ActiveWorkbook.Name
Open_wkbk_sheet = ActiveWorkbook.ActiveSheet.Name ' Set variable to the sheet you want to move
dest_file = getfile 'Call another routine to open the destination workbook
Msg = MsgBox("Moving, " & Open_wkbk_sheet & " to workbook, " & dest_file & ".", vbYesNo + vbInformation)
Workbooks(Open_wkbk).Sheets(Open_wkbk_sheet).Visible = True
Workbooks(Open_wkbk).Sheets(Open_wkbk_sheet).Move After:=Workbooks(dest_file).Sheets(1)
Application.Dialogs(xlDialogSaveAs).Show
End Sub

 

[Andrew Poulsom]

You can also use object variables to keep track of workbooks/worksheets, like this:

Option Explicit

Sub copysheet()
    Dim ShSource As Worksheet
    Dim dest_file As String
    Set ShSource = ActiveSheet ' Set object variable to the sheet you want to move
    dest_file = getfile 'Call another routine to open the destination workbook
    MsgBox "Moving, " & ShSource.Name & " to workbook, " & dest_file & ".", vbYesNo + vbInformation
    ShSource.Visible = True
    ShSource.Move After:=Workbooks(dest_file).Sheets(1)
    Application.Dialogs(xlDialogSaveAs).Show
End Sub

It is also a good idea to use Option Explicit at the top of your code to force variable declaration. This prevents errors caused by typos, and declaring your variables makes your code run faster if they are given a Datatype other than Variant (the default). To have Option Explicit appear automatically when you insert a module choose Tools, Options and on the Editor tab check "Require variable Declaration".

 

[jag108]

This looks like much nicer code than mine, the concept is so much easier than the way I did it. Some times we have to see to understand, I am very greatful for the oppertunity to see your example thank you.