Macro to move sheets

jag108

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can also use object variables to keep track of workbooks/worksheets, like this:

Code:
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

Active Member
Joined
May 14, 2002
Messages
429
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,144,770
Messages
5,726,187
Members
422,661
Latest member
foxleinlady

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top