Activating Worksheets In Different Workbooks

Apr 28, 2007

I have created a macro which unhides a sheet (Email) in one book and takes that info to #1 create an email and #2 open a new file and paste data into it.

Problem is, I want to go back to the first book and hide the sheet (Email) since other users will be using the macro. I keep getting a run time error 9 'Subscript out of range'.

I have my code below. I have commented out the last few lines that used to work before I had to open a new book first.

....

View 9 Replies


ADVERTISEMENT

Copy Worksheets From A Couple Of Workbooks To Other Workbooks

Jun 26, 2014

I have 25 files with certain worksheets that I need to move to 25 other files.

Worksheet 1, 2, 3 and 4 in Workbook A needs to be moved to Workbook A-2014 Worksheet 1, 2, 3 and 4 in Workbook B needs to be moved to Workbook B-2014 Worksheet 1, 2, 3 and 4 in Workbook C needs to be moved to Workbook C-2014 etc....

Is there a way to do this with a macro? Preferably I would like to do this automaticly - i.e. runing the macro from a master file that

1. Opens Workbook A copies the worksheets
2. Open Workbook A-2014 paste the sheets
3. Save and close Workbook A-2014
4. Close workbook A without saving

then doing the same for Workbook B, etc.

View 5 Replies View Related

VBA Code For Worksheets Within Workbooks

Jan 31, 2012

For example, I have 50 clients. In 1 workbook, I have a sheet for each client. When I'm finished with a particular client, I need their one sheet to place in their file and be done with it.

The current process is to open that 1 global workbook, copy and paste the sheet I need into a new workbook and go from there.

I was wondering if there is a simpler way to achieve this with VBA coding?

Ideally, I would like to click an object button and then be prompted to select a worksheet from a list that contains all current non-hidden worksheets.

Once I select a worksheet, I would then be prompted to save as and select a file path. That would save that specific client worksheet in its own file that I selected.

View 7 Replies View Related

Consolidating Several Worksheets From Several Workbooks

Jul 14, 2008

I have several workbooks (around 15) in one folder which all contain the same worksheets but with different data. The worksheets all have the same columns. What I would like is a way to consolidate each of the worksheets of the workbooks into one new workbook by running the code whenever I want it.

In other words I have workbook 1 till 9 which all have worksheet 1 till 3. And I would like to consolidate them into a new workbook with sheet 1 till showing all of the data.

View 9 Replies View Related

Compare Worksheets From Two Workbooks

Dec 21, 2009

I have a master tracking document that I use to record project information. My client sends me an updated schedule each week which may (or may not) have additional stores on it and some of the details of the stores may have changed.

I need a macro to capture these changes from the source spreadsheet (the one the client sends) and update the master tracker. The master tracker has a lot of additional columns of data that I add in myself about each project so I don't want to lose this information. The macro needs to see if the store on the source sheet is already on the master tracker and if it is then it needs to check to see if any of the columns below have changed.

If the store isn't on the master tracker then it needs to be added. There are around 750 stores on the master tracker at the moment so to do it manually takes forever!

Master Spreadsheet

Column A - Retail Region
Column B - Project Name
Column C - Postcode
Column D - Net Selling Area
Column E - Project Manager
Column F - Contractor
Column I - Start On Site
Column J - Launch Date


Source Spreadsheet

Column C - Retail Region
Column D - Project Name
Column I - Postcode
Column J - Net Selling Area
Column M - Project Manager
Column N - Contractor
Column P - Start On Site
Column Q - Launch Date

View 9 Replies View Related

Linking Worksheets In Different Workbooks

Jul 7, 2006

I have three workbooks stored in the same drive(shared drive). Let's call them 1.xls, 2.xls and 3.xls. Each workbook has one sheet.Column A in all sheets is the same. I want to make the following linking:

-Column A of the sheets in 2.xls and 3.xls to be linked and get data from column A of the sheet in 1.xls.
-Column B of 1.xls to be linked and get data from column B of 2.xls and column C of 1.xls to be linked to column B of 3.xls.

So far it sounds easy. What I cannot find is what to do when I insert a row in the sheet in 1.xls and write something in column A. I managed from DATA- IMPORT EXTERNAL DATA to refresh column A of the sheets in 2.xls and 3.xls, so that these columns contain the updated information. I cannot do the vice-versa procedure: for example, to insert something in column B of the sheet in 2.xls and refresh the values of column B of the sheet in 1.xls. The fact is that when I insert a row in 1.xls the right references get lost and move one cell up. I want the references to remain stable. In a way the sheets are interdependent.

View 2 Replies View Related

Delete Middle (same) Worksheets In Many Workbooks

May 8, 2014

In the attachments is the original format of the workbook, but the sheets are blanco. Did this on purpose, because the info is sensitive.

Is there a method to delete the middle sheets, 'Total1999' and 'Klad1'?

Is there a VBA code do this in a lot of workbooks, the format of all these books are the same.

Plus I'll place them in one map if it's necessary.

View 13 Replies View Related

Save Worksheets As Separate Workbooks

Dec 6, 2009

I have a workbook which have worksheets say A to J. I wanted it to be separated into 10 different workbooks A.xlsx, B.xlsx, C.xlsx and so on in drive C. Could anyone help me here?

View 6 Replies View Related

Copying And Cleaning Worksheets (across Workbooks)

Feb 2, 2012

I wanted to create a target workbook that is a subset of the source workbook

1) I want to specify a list of worksheets in the source workbook:

For Each WSCurrent In Sheets(Array("SheetA", "SheetB", "SheetC"))
Next WSCurrent

2) I want to them copy these sheets into another workbook (don't know how to code this)

3) I then want to hardcode all these sheets (I don't know the most efficient way to do this)

4) Lastly, I want to eliminate certain columns (can be fed through a hardcoded list of Columns to delete e.g. X, W, Z)

View 5 Replies View Related

Syntax For Referencing Other Workbooks / Worksheets

Jul 20, 2012

I'm trying to optimize code by avoiding activating other worksheets, but I'm running into a problem with a match function.

I'm using a workbook called "Template" and then opening another workbook called "DCP" and trying to use the match function to figure out what row data is on. I can get this first line of code to work:

HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, Sheets("Sheet1").Range("A:A"), 0)

However for that to work, I have to activate the other workbook. I want to avoid that and stay within the "Template" workbook. I think I need something like this:

HTML Code:
MatchedRowNumber = WorksheetFunction.Match(Combo, DCP.Sheets(DCPSheet).Range("A:A"), 0)

That one however doesn't work... looks like I have the wrong syntax.

Below is an excerpt for the code in case something is wrong with how I set the variables.

HTML Code:
Sub StockOrderByDCP()

Dim Template As Workbook
Dim DCP As Workbook

Dim MSS As Worksheet
Dim DCPSheet As Worksheet

[Code] ..........

View 4 Replies View Related

Copy Worksheets From Workbooks Using A Makro

Jun 3, 2008

I'm trying to copy worksheets from different workbooks to one workbook, but it doesnt work. This my code, can anyone spot the error?
The problem is that it stops at the workbook that all the worksheets are supposed to be copied to.

Sub Makro11()
'
' Makro11 Makro
' Makrot inspelat 2008-03-17 av White
'
' Kortkommando: Ctrl+w
'
namn = ActiveWorkbook.Name
ActiveSheet.Select
If ActiveSheet.Range("B3") = "" Then
ActiveSheet.Range("B3") = "v 16,"
End If
If ActiveSheet.Range("B3") = "v 15," Then
ActiveSheet.Range("B3") = "v 16,"
End If
ActiveSheet.Copy Before:=Workbooks( _
"KI.xls").Sheets(2)
Windows(namn).Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

View 9 Replies View Related

Collate Worksheets From Multiple Workbooks Into One

Feb 28, 2009

I have a few hundred multi sheet workbooks that have address data in one of the sheets that I wish to collate into a single worksheet to use as a mail merge with word.

View 5 Replies View Related

Macro To Combine Worksheets And Workbooks

Jul 12, 2006

I've got a macro that combines multiple worksheets and workbooks. Just a minor problem, the very last row of each worksheet is being deleted, and I can't seem to pinpoint the cause of the problem.

'This will copy data from all sheets of the selected workbooks
'To a sheet named 'Data' in the sheet in which the macro is run from

Dim pasterow As String
mainsheetname = ActiveWorkbook.Name
MsgBox ("Please select spreadsheets to combine")
filestoopen = Application. GetOpenFilename(MultiSelect:=True)
responseval = MsgBox("Do you want to leave the combined spreadsheets open?", vbYesNo)
Worksheets("Data").Select
Range("A1").Select
'open workbooks
For Each w In filestoopen...................

View 2 Replies View Related

Copy From Multiple Worksheets & Workbooks

Jul 18, 2006

I have a master workbook that has three sheets: Application, Equipment, Storage. I have over 500 other workbooks that have those same three sheets in them. Those workbooks also have other worksheets that I do not want. How can I create a macro that will open every one of those workbooks and copy over the data from each one of those 3 sheets into it's counter part in the master workbook?

View 3 Replies View Related

Save Worksheets To Individual Workbooks

Aug 22, 2006

I want to do is save each worksheet in a workbook to an individual workbook with the name of the worksheet. After executing the code below the strangest thing happened in that each saved workbook contains the lines ...

View 5 Replies View Related

Copy Worksheets Into Separate Workbooks

Sep 14, 2006

I have a workbook with multiple sheets and I want to make these sheets into separate workbooks. I need them to be saved in the same path as the original workbook and automatically named (same filenames as worksheet names). I tried a code from a VBA book, and I got "Object needed" error message.

View 3 Replies View Related

Allow Users To Save Specified Worksheets To Workbooks

Oct 1, 2006

Sub SheetArray()
'I need the code to bascially loop through the workbook _
identify the worksheets With Priority In thier name And _
Then create an array variable such As _
Sheets(Array("Priority A1", "Priority A2", "Priority A3") _
At this point I can Then select the sheets And save them off To _
another workbook. The issue I have appears simple but I 'm lost as _
To it 's solution. Any help would be greatly appreciated as I have _
been stuck on this For days

Dim ws As Worksheet
Dim ShShortName As String
Dim SheetString As String

For Each ws In Worksheets
ShShortName = Left(ws.Name, 8)
'Debug.Print ShShortName
If ShShortName = "Priority" Then
SheetString = SheetString + ws.Name
End If
Next
Debug.Print SheetString
'basically I'd like to use the SheetString value above to _
create the arrray variable As above In the comments. The _
reason I want it To use the Loop To assign the variable Is because _
at any one time I 'm not sure as to how many priority sheets I may _
have In the workbook, And this will change constantly

End Sub

View 8 Replies View Related

Count Worksheets In All Open Workbooks

Mar 2, 2008

I am trying to figure out how to count all open workbooks, then count all worksheets within the workbooks and come up with the total amount of worksheets in a message box. I know that it sounds very simple and probably is, but I really can't figure it out.

View 6 Replies View Related

Macros To Protect / Unprotect Worksheets And Workbooks

Jul 20, 2005

The first macro protects all sheets in a Spreadsheet and the second unprotects. The the next Macro Protects the enitre workbook and likewise the next one Unprotects. All using passwords.

VB:
Dim ws As Worksheet
Sub ProtectAll()
Dim S As Object

[Code].....

View 9 Replies View Related

Using Autofilter Values To Create New Worksheets Or Workbooks

May 13, 2014

I often have a table (sometimes formatted as a table, sometimes just data arranged like a table but not formatted as a table in Excel) where I'd like to create individual worksheets (eventually workbooks) based on the information in one of the columns. Take for instance the attached file. I'd like to create a macro to create worksheets (or preferably workbooks) based on data in the Region column. So workbook 1 would be something like North Region Sales 2014, and contain only the data for the North region. Workbook 2 would be something like South Region Sales 2014, and contain only the data for the South region, and so on. Sometimes I might need to create these based off the Region field, another time I might need to do it based off of the Salesperson.

Region Sales 2014.xlsx

To do this currently, I'd use the filter and unselect whichever data I want to keep and then delete all the remaining (visible) rows. When I unfilter, I'm only left with the data I want. This works, but it takes a long time when working with 50 or more "Regions" and large amounts of data.

View 14 Replies View Related

Copy All Worksheets In Lots Of Workbooks Into One Workbook?

May 8, 2013

All i need to do is copy all worksheets in lots of workbooks into one workbook with multiple sheets, easy right!!

View 2 Replies View Related

Macro To Copy Worksheets Into Individual Workbooks

May 27, 2008

Have a workbook with approximately 25 worksheets in it.

I want to copy/paste/value each worksheet and save it as it's own workbook.

My code doesn't work, it hangs up on ws.copy every single time, on Excel 2007 and prior versions.

Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "t:dir1expenses" & ws.Name & ".xls"
wb.Close False
Next ws
End Sub

View 9 Replies View Related

Conditional Copying Of Columns Between Worksheets/Workbooks

Jun 20, 2006

i want to copy data from one excel workbook to other (or ever between diff sheet wud do ) on basis of search on a complete column by picking data one by one from other column & on finding the exact match copy 4 columns form one workbook(or sheet) to other. this has to be done 4 a bulk of data (1000+) so manually is next to impossible.

View 2 Replies View Related

Create Workbooks & Worksheets For Each Group In Table

Jan 17, 2008

I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.

The columns are as follows:

Table
Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length

From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.

View 9 Replies View Related

Accessing Data From Worksheets In Multiple Workbooks

Jun 27, 2008

I have read all the rules, and the guidelines for writting a thread title, and can only hope that my title meets those criteria (Dave, if they do not please do not ban me). I am fairly new to VBA, I have been able to edit other peoples code, and have created a few macros myself, and although I think I know how to use a loop and arrays, I sometimes lack some of the basics, this may be one such case. That said here is the problem I am facing.

I am creating a database, and as such I am sent several workbooks, with each workbook containning several worksheets. Within these workbooks, the naming convention of the worksheets is consistent, though each workbook may contain different number of worksheets. (the name of the worksheets represents a specific size/format).

What I have is a "Setup sheet" that I would like populated with all the data from specific worksheets in the various workbooks. So I would need to be able to get the macro to check all the workbooks, and if it finds a worksheet entitled "Sheet B" (for example) that it copies all that data into the "Setup sheet", and progresses onto the next workbook. I have the loop working and am fairly confident on this side of things, though where I am struggling is getting the Macro to know what worksheet to look at when going through the workbooks.

I need the macro to be fairly dynamic in which Worksheet it looks at so I don't want to simply hardcode the Worksheet name into the macro, rather I would like to be able to specify the worksheet name based on a cell value in the "Setup sheet". so going back to my earlier example if the cell value in the "Setup sheet" = "Sheet B" then the macro should get data from the worksheets entitled "SheetB" in the workbooks, if the cell value in the "Setup sheet" = "Sheet F" then the macro should get data from the worksheets entitled "Sheet F".

View 2 Replies View Related

Skip Copy Worksheets To Other Workbooks If Duplicates Present?

Mar 18, 2014

I am trying to copy worksheets from a master workbook to numerous other destination workbooks. I would like the code to check if the destination workbooks already contain any of the sheets being copied and if this is the case, miss out that sheet and move on to the next. Currently, if duplicate sheets are present, a second sheet is created and copied to the destination file.

VB:
Sub CopyTrend()
Dim Filename1, sFileNameAndPath, sThisWorkbookName As String

[Code].....

View 3 Replies View Related

Copy Similar Named Worksheets To Seperate Workbooks

Dec 23, 2008

I have an application where I create up to 400 worksheets based on imported data.

These worksheets are named programatically with a main name and a sheet index number (to ensure no duplication of sheet names).

Typical Sheet names are "VARIOUS (1)"; "VARIOUS (2)"; "PART 1000 (3)"; "PART 1000 (4)"; "PART 1001 (5)"; "PART 1001 (6)" etc.

I want to be able to loop through all the sheets and copy similar named worksheets to seperate workbooks. So all the VARIOUS go to one workbook, all the PART 1000 goto another, and all the PART 1001 goto another. I can only determine the worksheet names by looping through all sheets - as I am not sure what they will be called because it is done programmatically. ALL sheets will contain a "(n)" where n is the sheet number, so I believe can extract similar names from the string preceding the left bracket.

At the moment I just lump ALL the worksheets irrespective of name to a single workbook creating an array and using the SPLIT function. This is very efficient and what it does, and I still want to use the SPLIT function and arrays.

this is the code I currently use, any help much appreciated!!! Please tell me how to modify this existing code to clump together similar names and copy - I guess I will need to loop multiple times to achieve this.

For Each mySheet In ActiveWorkbook.Sheets

Range("Arc").Value = "'" & Range("Arc") & "," & mySheet.Name
If Left(Range("Arc").Value, 1) = "," Then
Range("Arc").Value = "'" & Right(Range("Arc").Value, Len(Range("Arc").Value) - 1)
End If
Next

Sheets(Split(Range("Arc").Value, ",")).Select
Sheets(Split(Range("Arc").Value, ",")).Copy

View 5 Replies View Related

Combine Data From Specific Worksheets And Multiple Workbooks In Various Directories?

Feb 22, 2014

I have a master workbook that has been set up to mirror the structure of a single worksheet in various other workbooks saved in different directory locations. I need some VBA code to retreive specific data from a specified worksheet in multiple workbooks which are saved in different directories and then copy the data to the master workbook, listing each data set one after another. I do not want to open any of the source workbooks to acheive this.

I attach two example workbooks to better explain:

The code has to look in various sub directories to find the relevant workbooks, (Source1) then find the specified worksheet, (Stock) and copy only rows that have data from column B to O. The data needs to be copied to the master workbook, (master) from all the source workbooks as a list with no space.

View 4 Replies View Related

Changing Default Font And Cell Sizes In New Workbooks / Worksheets

Mar 17, 2012

How to change these defaults...

It involved opening a new workbook and changing the number of worksheets in a new workbook, cell sizes, font, and font sizes... and then saving the workbook in one of the system folders under the name "workbook" to change the default workbook attributes, or "worksheet" to change default worksheet attributes.

View 3 Replies View Related

Copy Data From Different Workbooks / Worksheets And Paste In Master Workbook?

Apr 29, 2014

I get 'x' number of workbooks(with one sheet only) everyweek from which I need to copy data and paste to a master worksheet. (SCREEN CAPTURE BELOW)

I am unable to write the code myself as I have never worked on VBA and am only a beginner.

Part I:

The data I need to copy starts from the 19th row (A19:H19). The end point is determined by the row just before the row that has the words "Calibration Request" in it.

Part II:
Just below the data that was pasted from Part I, the data from 2 rows below the words "Calibration Request" needs to be pasted. The end point for this would be a blank row encounter.

Also some of the rows and columns are merged.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved