How To Pass These Worksheets To Another Workbook

Jul 10, 2014

I have tried to pass the worksheets to another workbook by copying the sheets and passing it to the other workbook with Excel's own tool. Whenever I try this I get an error with the API XML request.

View 8 Replies


Pass Data Between Worksheets

Jul 1, 2014

How can I pass data from a specific cell in one workbook to another worksheet using a hypertext link or command button. For example How would I be able to pass the following data from Workbook 1 R1C1 - Test Data to the same cell in Workbook 2.

View 1 Replies View Related

Pass Workbook Name After Save

Aug 13, 2008

I'm trying to develop a macro to perform some actions on a workbook after it's been saved, this using the ontime method, along the lines of: ....

View 9 Replies View Related

Pass Workbook Variable Between Subs

Jul 11, 2007

This is the code after editing to make it more clear

Public Sub 1()
BookA= activeworkbook. name
End Sub

Public Sub 2()

With BookB. sheets(1)
End Sub()

At the end of public sub 1, BookB is the active workbook. What I want to do in public sub 2 is to copy some data from BookA to BookB. Unfortunately, when moving from public sub 1 to public sub 2, BookA needs to be defined again. The code above is the code that I use in my add-ins. I figured out for non add-ins code I can define BookA with when BookB is active as I before work with BookA. This does not apply for add-ins as thisworkbook will refer to my add-ins code. Is there anyway of keeping definition of BookA is constant from one public sub to another public sub? This is simplified code. In fact, I can't merge public sub 2 with public sub 1 due to some reason which I don't say it here.

View 6 Replies View Related

Pass Protection From A Master To A Slave Workbook

May 7, 2009

I have a master workbook with which I can produce slaves from but I am looking at keeping certain cells protected to stop deletion (dates etc). What I require is for the top 5 rows to be protected on each of the 4 week sheets where the dates etc are. I had played with protecting all the sheets and with unprotect then protect on the master but it did not carry over to the newly created slave, could someone point me in the right direction . SOLVED: The code is not pretty but functional.

View 2 Replies View Related

Loop Across Worksheets: Perform The Same Process To All The Worksheets In My Workbook

Aug 10, 2009

I'm trying to perform the same process to all the worksheets in my workbook. This is the code I have now, but it will only apply to the single active worksheet:

View 2 Replies View Related

Copy Worksheets From Active Workbook To A New Workbook

Apr 22, 2009

I am trying to code a Macro so that i can take all the worsheets and save them as individual Workbooks. I wrote a macro that appeared to work, but, after it saves the first sheet as a workbook, i get a debug error.

MS VB Script error:
Runtime error '9':
Subscript out of range

Any advise would be greatly appreciated.

Thank you

Code is below..

Sub saveall()
For Each ws In ActiveWorkbook.Worksheets

ThisFN = "C:Documents and SettingsUserDesktop" & ws.Name & ".xls"
I = I + 1
ActiveWorkbook.SaveAs Filename:= _
ThisFN, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _

Next ws

End Sub

View 9 Replies View Related

Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String


Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

View 4 Replies View Related

3 Worksheets In One Workbook

Dec 9, 2009

I have 3 worksheets in one workbook. I need to extract 10 random rows of information from about 193 rows in sheet 1, and post it in sheet 2 and I can't seem to find a way to make this happen correctly.

View 9 Replies View Related

COUNTIF Across All Worksheets Of Same Workbook?

Jan 9, 2014

I need to count the frequency/occurance of strings "Manager", "Clerk", "HR" etc. that occur in the exact same column in multiple worksheets of a single workbook. COUNTIF works ok on a single sheet query but the formula is getting bigger if there are number of worksheets.

simplyfing the formula. Also is there anyway to put the formula on the column instead of row wise. I can use a single formula on the column instead on every row in Adminsheet.

To be clear my requirement is to count the occurrence of a search strings listed in Adminsheet column A across all worksheets and display count in Adminsheet column B

View 8 Replies View Related

Several Worksheets Same Workbook Same Formulas

Mar 4, 2009

I use excel 2003 SP3. I've created invoices that have several (very basic), but different formulas in the last column of the worksheet. I have the same customers every month so I use the same monthly workbook for all invoices. Since each invoice has the same formulas, in the same cells, I used the SHIFT key while selecting the entire range of worksheets (invoices). After they have been selected, I entered the SUM formula in a cell so it would automatically be entered in all of the worksheets (invoices).

When I select the same cell in all the worksheets (invoices) I can see that the formula is there. However, when I enter a value that the formula should be adding, it doesn't. But if I select the cell with the formula in it, the formula shows in the formula bar. When I put my cursor at the end of the formula and hit ENTER, the formula then works. So I have to select each formula, in each cell, on every worksheet, hit ENTER, and then, if I need to change a value, I have to repeat the process. I am definitely missing something, and I'm sure it's quite obvious and simple.

View 3 Replies View Related

Arrange 2 Worksheets In The Same Workbook

Mar 27, 2009

Can you arrange 2 worksheets in the same workbook so they are side by side, like you can the way you can tile two different workbooks?

View 2 Replies View Related

Merge Worksheets Into One Within Same Workbook

Jul 8, 2009

how to merge data from all worksheets into one new in the same workbook, see example in attach. There are 5 worksheets in the test workbook. Each worksheet has name of “Sheetx”,X=number of worksheet. Number of Row in each worksheet is uncertain, but it is always less than 35 (I will say it is “safe” range to prevent from missing data). Requirement: Vertically merge data from A1:F35 (including the “blank”) to “TEST”.

I am using Excel 2007 at office. By the way, I am NOT a excel expert, while posting your answer, could you please also explain how to do it step by step?

View 5 Replies View Related

Protecting Worksheets In A Workbook

Jan 20, 2010

I have a workbook with several worksheets in it. One worksheet for each manager. I only want the individual manager to see their individual worksheet and not those for the others. Is it possible to hide the worksheet and unhide it using an individual password, a seperate one for each manager ?

or alternativly, is it possible to hide say the columns and give each manager their own password that only allows them to unhide the columns for their worksheet. I really dont want to have to send out several seperate versions, just sending one workbook but passwording the individual worksheets within it would be ideal.

View 3 Replies View Related

Copy Worksheets To New Workbook

May 21, 2008

I have a workbook containing several worksheets I want to copy to a workbook of their own and save under a specific name/password through a macro.

However, I have five difficulties:

1) I want to new workbook to contain only 1 worksheet. So, only the worksheet copied from the original workbook, and no other worksheets.

2) I want to be able to manually select the destination file for the copied workbook, however. This should be the same file for all workbooks.

3) I want the data copied from original worksheet to be ‘pasted as value’ only, so no formulas in the new workbook.

4) I want the new workbook to be read-only. Therefore I want the workbook to be /locked and password protected. So, the password should be only for altering cells, not for opening the workbook. See also point below.

5) Regarding the naming and passwording part I have made a matrix table in a worksheet named: “Rekeningen” (this worksheet is in the original workbook). This table contains three columns:

Column A, cells 40:65, contains the names of the worksheets I want to copy
Column B, cells 40:65, contains the names of the new workbooks
Column C, cells 40:65, contains the passwords of the new workbooks

So for example ....

View 9 Replies View Related

Formatting Changes To All Worksheets In Workbook

Jul 21, 2013

Is it possible to apply changes to formatting (column width, text size) to all worksheets within a workbook? I have a workbook with 50 worksheets--1 for each state--and I want the formatting to be standard throughout, and I want to do it quickly.

View 3 Replies View Related

List The Worksheets In A Workbook

Feb 28, 2007

I have around 40 to 50 worksheets in a workbook. Is there a quick way of listing the names of all the worksheets in a single worksheet instead of typing the sheet names one by one?

View 2 Replies View Related

Organizing Worksheets In A Workbook

Apr 19, 2007

Is there a way to sort the worksheets in a workbook after they have already been entered. My clerk put 200+ worksheets in one workbook....out of order.

View 7 Replies View Related

Copy All WorkSheets To Another Workbook

Sep 5, 2007

Assume 2 Workbooks - the Active one and "Book2".

I mannaged to copy ALL the sheets from the ActiveWorkBook to "Book2" with the following code - but as you can see the code copies each of them AFTER the last sheet in "Book2".

Sub Copy_Sheets()
' Copy All sheets from ActiveWorkbook to "Book2.xls"
For Each SH In ActiveWorkbook.Sheets
SH.Copy After:=Workbooks("Book2").Sheets(Workbooks("Book2").Sheets.Count)
End Sub

How can I copy them to "BEFORE", lets say Sheet(1) in "Book2" ?

I tried to change the SH.Copy command to somthing that sounds logic to me, such as:

SH.Copy Before:=Workbooks("Book2.xls").Sheets(1)

but got the: "Run-time error '9' - Subscript out of range".

View 5 Replies View Related

Shortcut Key To Switch Between Worksheets Within Workbook?

May 22, 2014

Any shortcut key that allows you to switch between worksheets within the same workbook?

Found the shortcut - CTRL and Page Up / Page Down

View 1 Replies View Related

Mirror Data Between Worksheets In Same Workbook?

Jun 25, 2013

Using Office 2011 for mac, but felt this question was best suited for general.

Here is the situation.

I have a workbook with three sheets. It's a price/invoice/labor workbook.

Sheet 1 has all the data, including my cost and loss/gain fields
Sheet 2 has labor costs and the totals are linked to Sheet1
Sheet 3 is the customer copy of the first sheet. It does not include wholesale and profit info.

I'm trying to link the cells so that when info changes in Sheet 1, it's reflected, in real time on Sheet3

Right now I'm using a formula per cell ='Sheet1'!XXX (where XXX = the cell/column location, ie D15). The first column I started has this formula in every cell, about 100 in all, copied by hand and changed to reflect. It works, but it seems like there has to be an easier way to mirror or link the data. I tried pasting special with links, but continue to get errors as the link pather includes the entire file data path from the local machine.

I still have about 200 cells left and I'm dreading doing this all by hand. Also, If I add a new row to sheet1, this will not reflect in sheet 3.

View 7 Replies View Related

How To Reference Worksheets Inside Workbook

Jul 3, 2013

I do a payroll workbook for my small company. Each sheet inside the workbook is a month. Each sheet totals the monthly labor categories. I would like to keep a "year to date" total on each employee and therefore would have to reference each sheet.

Setting up a formula (or tell me where to look) on how to total worksheets inside a workbook?

Example: the workbook contains a total of 12 sheets, representing the 12 months of the year. Add "cell reference" from sheet 1 to sheet 2 to sheet 3 to reach a total, then when sheet 4 is completed it contains the cumulative totals of all 4 sheets, etc., etc. Where I can look at this "year to date total" and compare to last years "year to date total".

View 2 Replies View Related

Filter Data Into Different Worksheets In New Workbook

Feb 19, 2014

Macro to filter (extract) data into different worksheets based on Status on Column G in a new workbook. The new workbook can be saved in the same folder where the existing one is saved.

I have attached the Rawdata file and the sample file for reference..

View 5 Replies View Related

Copy Active Worksheets Into One New Workbook

Jul 3, 2007

I have 16 proposals that are either hidden or visible in a proposal generator. I need to be able to copy only the visible worksheets over to a new workbook that uses a name from a cell inside the proposal generator. Then a save as prompt to force the user to save the file wherever they choose.

View 14 Replies View Related

Excel VBA To Create New Workbook And Name Worksheets Within

Nov 15, 2011

Code that will autofilter my sheet for rows that contain either a 1,2,3, or 4 in Column "Z". Then, create a new workbook for each unique value in column "O" and save that to a folder on my desktop named "Contracts". Next, while still filtered by by "Z" and for every unique value in column "O", filter and name a sheet in the new workbook for each unique value in column "B". Finally, while the main sheet is filtered I need to copy and paste the visible cells (minus header row) from columns C,D,E,F,G,H,S, and U to consecutive columns in the newly created worksheet beginning in cell "A9".

I am using excel to create some contracts for a few partners. Each partner could work with several of my clients and I need to give each partner a personalized contract for each of my clients with which they work.

I hope my description of the problem is not too confusing. If so, let me know and I will try to clarify exactly what I am trying to say.

I have tried to combine some code that I already have but, being a newb with loops, I can't seem to get it to do everything.

View 1 Replies View Related

Separate Codes On Different Worksheets In Same Workbook?

Feb 29, 2012

I am running a Macro to fix my hyperlinks in excel, and I have several worksheets that I want to preform the same function for but with different new and old strings. The code I'm running is the hyperlink fix code:

Sub Fix192Hyperlinks()
Dim OldStr As String, NewStr As String
Dim hyp As Hyperlink


View 3 Replies View Related

Update Multiple Worksheets In Same Workbook At Once?

Apr 23, 2012

I have faced a need to update several worksheets with the same format at once, like with copy paper, when i enter data in the first worksheet.

I have pre made worksheets that are identical by format.

There are probably many way to achieve what i want, but i need to find the easiest, less memory consuming method to do it.

View 4 Replies View Related

Repeat Heading Row To Other Worksheets In Workbook

Jun 30, 2012

I know this is probably very simple and basic but I have a workbook with a total of 8 worksheets.

The first sheet is going to be a summary of information contained on the 7 other worksheets.

All 7 sheets have the same header row and I would like to have it so that all 7 rows repeat the same headings and if I add more sheets they automatically also have the heading row appear.

Is this possible other than doing a copy paste when you insert a new sheet? Also is it possible to code it so that should a new colum (heading) be added to the first worksheet it would automatically be added to all the other sheets?

The first summary sheet will have headings that reflect the name of the employee and then the names of the following worksheets as the header row and will gather information about the progress of each employee on fulfilling the requirements setup on the other worksheets.... that is yet another question on how to get it to do this.... which I will post later..

View 4 Replies View Related

Set Macro For All Worksheets (active Workbook)

Nov 14, 2006

Sub display_all_false()
With Application
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
.ShowWindowsInTaskbar = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Control Toolbox").Visible = False
.CommandBars("Drawing").Visible = False
End With
End Sub

But, when I run the code, seems there're run-time errors detected at these codes:
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayOutline = False
.DisplayZeros = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False

1. Is there any way to correct these codes?
2. Does the bolded codes applied to ActiveWindow (Worksheet) only (eg. With ActiveWindow ...)? Can I change it to ActiveWorkbook (perhaps)?
3. Can the command menus (File Edit View Insert Format ...) be hidden also?
4. Does looping method for each worksheet

View 9 Replies View Related

Looping Through Specific Worksheets In A Workbook

Oct 27, 2007

Looking for For Loop to loop through 5 specific worksheets in a work book.

Something like this, but can't find right syntax:

For iCounter = 1 to 5
If = "Recap" & iCounter Then
'do stuff
End If
Next iCounter

View 4 Replies View Related

Copyrights 2005-15, All rights reserved