Excel Tips - Copy Sheet To New Workbook

I've a workbook approaching the 10Mb mark, which is causing speed & therefore
usability issues over our network. It consists mainly of links & formulae (I
have already deleted all the macros). Has anyone got any tips or ideas on
how to reduce the size of the file?

Hi,

I have been trying to copy an excel sheet to another excel sheet through VBA macro. I am new to this macro concept. Currently I have using Ms-Excel 2004 in MAC.

Below is the code that I wanted it to execute but it is showing some problem i.e Run Time Error 9 subscript out of range'

/*****Start of Code******/
Sub Mover2()
ActiveSheet.Move Befo =Workbooks("/Users/jibanendu/desktop/abc.xls").Sheets(1)
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
End Sub

/*****End of Code******/

Please guide me through and help me out to resolve this error,cause I am in serious need of this.

Thanx in advance,
Jibanendu

Hello,

I was wondering if someone could help me with a small problem please.

I have set up a macro to copy a section from 31 sheets in a workbook and copy them to a final sheet at the end. This is working ok except that the macro doesn't know that the final sheet shouldn't be included so its also copied.

Is there a way to tell excel not to copy the last sheet?

Here is the macro, I'm new to VBA so apologies if the code is crude, but I've had some wonderful help from the people on here before.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' copydata Macro
' Macro recorded 18/06/2010 by DAWS
'
' Keyboard Shortcut: Ctrl+Shift+C


    Sheet32.Activate
    Range("I15:U34").Select
    Selection.ClearContents
    
For Each ws In ActiveWorkbook.Worksheets
    ws.Select

    Range("I15:U19").Select
    Selection.Copy
    Sheet32.Activate
    Range("I15").Select
    Range("I30").End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Next ws

End Sub

maybe is a bug
when i copy a sheet in same workbook i observed a temporary block XP Excel
program and nothing happend
when i try to close Excel, he say that if i wish to save a new workbook wich
i did not create in that session
maybe Excel was create that workbook for himself

Just for know why is that happening...

I have to workbooks Workbook1 and Workbook2. Both workbook have 1 sheet each. I have to pick each value from ColumnA in Workbook1 and check if it is in ColumnC in Workbook2 and if is there do not copy otherwise copy them to Workbook2 at the end of that column.

I am totally new to excel macros and tried my hand with following some samples, but looks like I am messing too much.

Please help.

Hi,

Workbook 1
File and sheet names are always different. Only one sheet to start with. Macro to run from here and return here...

Workbook 2
File and sheet names are always the same.

I am trying to create a macro which in current workbook (1) creates a new sheet, opens another workbook (2), copies data, goes back to workbook 1 and pastes data on the new sheet. Goes back to first/original sheet in same workbook 1 (always different sheet name) and inserts a formula. Closes workbook 2.

I tried to write the following myself but get Compile Error: Expected Function or variable. With .activate highlighted. I expect that .Select will also produce an error. See blue text in code below.

Can someone advice how to correct my erros or do this differently? (VBA newbie/beginner)

Code:

 
Sub MacroMultLoc()
'
' MacroMultLoc Macro
' Macro recorded 29-09-2010 by 
'
'
Dim Bk1 As Workbook
Dim Sh1 As Worksheet
Set Bk1 = ThisWorkbook
Set Sh1 = ActiveSheet
    Sheets.Add
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "ItemLocations"
    Range("A1").Select
    ChDir "K:\Sample\DoNotMove"
    Workbooks.Open Filename:="K:\Sample\DoNotMove\ItemsLocationReport.xls"
    Columns("A:F").Select
    Selection.Copy
 
     With Bk1.  Activate 
 
    ActiveSheet.Paste
    Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Warehouse"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "ItemNo"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Amount"
    Range("G1").Select
 
     With Sh1.Select 
 
    Dim LR As Long
    LR = Range("L" & Rows.Count).End(xlUp).Row
    Range("p2:p" & LR).FormulaR1C1 = "=IF(SUMPRODUCT((ItemLocations!C[-14]=RC[-4])*(ItemLocations!C[-13]=RC[4]))>0,""No"",""Yes"")"
 
    ItemsLocationReport.xls.Close savechanges:=False
 
    Range("L1").Select
 
End Sub

Thanks

Hi everyone, there is a lot on this topic , but cant find much to help with the "All open" part...

Im trying to create a macro that will copy all open worksheets from one workbook (workbook1) to another open workbook (workbook2). That parts doesnt seem to hard, but the stipulations are, it needs to import the tab names (as they are in workbook1) to workbook2, and copy over the formulas without the referenced sheet [sheet1], or in better words remove the brackets and all that is inbetween, but still leaving the formula.

Thanks everyine in advacne for any help or direction.

Also is it even possible to copy those worksheets (tabs) from a path, without even opening them?

Re-worded:
Trying to open a worhseet from a path,

then unhide all sheets then loop through all open workbooks excluding the "this workbook" (becasue the names of workbooks change based on date daily)

Then copy all open worksheets on the newly opened workbook over to the existing workbook (keeping all the original tab names) at the end of the workbook.

Most importantly leaving the formulas and hopyfully removing and [Brackets with refernced paths]

Object:
Save BarChart worksheet in workbook 1 on Cdrive to Wdrive. I want only the BarChart worksheet to copy to workbook 1 on Wdrive.

After making changes to BarChart Worksheet, I want to save it to another drive. The code as demonstrated below does that, but copies all worksheets. how do I save and copy only the BarChart sheet to the other drive? Help.

The code below is the code I am using. Can you fix it so it does what it is supposed. I cannot figure out

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Saves the current file to a backup folder and the default folder
'Note that any backup is overwritten
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="W:\Mental Health\MHTC\MedDir_do_not_delete\leamonwebtest.xls" & _
ActiveWorkbook.Name
ActiveWorkbook.Save
Application.DisplayAlerts = True
End Sub

I need help with VBA that will open a workbook and copy the data from each cell into another workbook and stop after the last row of data. The number of rows vary, however the number of colums do not change. The information where the data will be copied will be to Master spreadsheet and the data in the spreadsheet will be replaced daily. Below is what I have so far. Can anyone help!!! PLEASE!!!

Code:

Sub Data()

    Dim wkbData     As Workbook
    Dim cell        As Range
    Do Until True
        Set wkbData = Workbooks.Open(Filename:="F:\Datadump.xls", ReadOnly:=True)
                For Each cell In wkbData.Worksheets("Sheet 1").Range(wkbData.Worksheets("Sheet 1").Range("A1"), wkbData.Worksheets("Sheet 1").Range("AT65536").End(xlUp))
                cell.Range("A1:J1").Copy
                ThisWorkbook.Worksheets("Sheet 1").Range("A1").PasteSpecial _
                        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            Next cell
            wkbData.Close False
            End If
    Loop
     ActiveWorkbook.Close SaveChanges:=True
    ActiveWorkbook.Saved = True
End Sub

Hi all,

i need a macro to copy sheet1,sheet2 & sheet3 from active workbook to a new workbook(past special only values) in c drive.
The name of new workbook should be newworkbook10012010.xlsx ie if he macro is run on 10 jan 2010.

Thanks