#1
|
|||
|
|||
Macro to create two copies of worksheet, then run macro.
Hi, I searched the forum but haven't been able to figure out what I'm looking for. I'm sure this is super simple for most of you but my excel skills are hit or miss. What I'm looking to do is make two copies of a worksheet (to make three total) then rename them based on the file name. Then run the same three macros in each one of the three worksheets. Pretty simple right? But I discovered that the copying part throws an error when I use it in other files because the actual file name of the original file is part of the code. how can I get this to work on any file name? below is the code, and attached is a similar file that I want to use this macro on. I can't attach the original file this macro was created on because I accidentally overwrote it Thank you.
Code:
Sub ThreeWaySplit() ' ' ThreeWaySplit Macro ' ' Sheets("DL_8_Auburn Oven 3 TUS_01282019").Select Sheets("DL_8_Auburn Oven 3 TUS_01282019").Copy After:=Sheets(1) Sheets("DL_8_Auburn Oven 3 TUS_01282019").Select Sheets("DL_8_Auburn Oven 3 TUS_01282019").Name = "Auburn Oven 3 TUS_260" Range("F33").Select Sheets("DL_8_Auburn Oven 3 TUS_0128 (2").Select Sheets("DL_8_Auburn Oven 3 TUS_0128 (2").Name = "Auburn Oven 3 TUS_350" Sheets("Auburn Oven 3 TUS_260").Select Sheets("Auburn Oven 3 TUS_260").Name = "Auburn Oven 3 TUS_260" Sheets("Auburn Oven 3 TUS_260").Select Sheets("Auburn Oven 3 TUS_260").Copy After:=Sheets(2) Sheets("Auburn Oven 3 TUS_260 (2)").Select Sheets("Auburn Oven 3 TUS_260 (2)").Name = "Auburn Oven 3 TUS_450" Sheets("Auburn Oven 3 TUS_260").Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" Sheets("Auburn Oven 3 TUS_350").Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" Sheets("Auburn Oven 3 TUS_450").Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" End Sub Last edited by BCrenshaw; 01-30-2019 at 10:31 AM. |
#2
|
||||
|
||||
Hi,
When posting code, please wrap it with code tags ( Edit code - select code - click the #button.) It keeps the macro's structure and makes it easy to copy and handle. Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Quote:
I tried to upload the .csv file and didn't notice that it's not a valid format. It's there now. |
#5
|
|||
|
|||
Perhaps something like this using variables to refer to the worksheets.
When you make a copy of a sheet, the copy automatically becomes the active sheet. Code:
Sub ThreeWaySplit() Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet Dim arr As Variant 'create copies, rename sheets 'original sheet Set ws1 = ActiveSheet 'break apart the original sheet name into an array arr = Split(ws1.Name, "_") 'rename original ws1.Name = arr(2) & "_260" 'first copy ws1.Copy After:=Sheets(1) ActiveSheet.Name = arr(2) & "_350" Set ws2 = ActiveSheet 'second copy ws1.Copy After:=Sheets(2) ActiveSheet.Name = arr(2) & "_450" Set ws3 = ActiveSheet 'deal with original sheet With ws1 .Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" End With 'deal with first copy With ws2 .Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" End With 'deal with second copy With ws3 .Select Application.Run "PERSONAL.XLS!Survey40wire" Application.Run "PERSONAL.XLS!Survey20WireShrinkRunFirst" Application.Run "PERSONAL.XLS!Survey20WirePortaitRunSecond" End With End Sub |
#6
|
|||
|
|||
Ah that makes sense. It worked perfectly! Thanks NoSparks!
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Macro to print a value in the duplicate and triplicate copies only | Marcia | Excel Programming | 2 | 09-26-2018 02:11 PM |
Macro for printing 2 copies each on NCR paper for many different word docs | Marq | Word VBA | 9 | 05-22-2017 08:31 PM |
Looking for Help to Create a Macro (Sort) | rsrasc | Word VBA | 5 | 04-16-2014 03:25 AM |
Macro to conditionally create or go to worksheet | Reinaldo123 | Excel Programming | 1 | 07-06-2012 07:23 AM |
macro for comparing data from 3 columns and pasting into another worksheet | ashukla | Excel | 1 | 06-24-2009 05:01 PM |