Copy Sheet - Save As - 400 Error
Jun 13, 2008
When I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.
Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "K:3. DesignSupply OrdersLog"
For Each sht In ThisWorkbook.Worksheets
If sht.Range("D1").Value "" Then
sht.Copy
With ActiveWorkbook
.SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
.Close
End With
End If
Next sht...................
View 9 Replies
ADVERTISEMENT
Apr 9, 2014
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB:
Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name
[Code].....
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
View 7 Replies
View Related
Nov 7, 2006
I have a workbook with one sheet. Some of the information in this sheet do I want do move to a new workbook, whit only one sheet. Then save the sheet as a CSV file.
I’m sure that this is simple, but I am very new in VBA.
So can someone help me with how to write a macro that create a new file whit only one sheet, and than saves it as a CSV file?
View 5 Replies
View Related
Sep 28, 2007
I need some code that copies from one sheet into another sheet... I am thinking to have a middle sheet with all the code pulling the data from "infosheet" into another sheet "vendorsheet". I'm just wondering if this is the right way to approach this issue.. because the vendor is going to want to use the sheet and we want to automate this operation.
This is what I have so far... but this is on the actual file that we are working with so I want it to be from a middle file. It creates a new file but it doesn't save it also... I'm not sure whats wrong here so any help is greatly appreciated!
Private Sub Workbook_Open()
Copy
End Sub
Sub Copy()
'
Application.CutCopyMode = False
Sheets("infosheet").Select
Range("A1").Select
Range("A1:G50").Select
Sheets("infosheet").Select
Sheets("infosheet").Copy
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.Copy
SaveAs1
End Sub
View 4 Replies
View Related
Sep 11, 2007
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies
View Related
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
View Related
Aug 27, 2012
I need VBA code for the following - I have a worksheet with seven colums of data (A to G) - I need to copy the first column (A) from the active worksheet then open master workbook called 'master' and paste the data in to column D - then save the 'master' as the name in cell Z1 of the 'master' workbook. Once this has been been completed I need to repeat the process but this time copying column (B) and so on.
View 4 Replies
View Related
Jul 5, 2009
I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.
Currently, when I book keep on a particular day, I open the template.xls workbook;
draft my journals;
and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.
With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).
Upon clicking a button, I would like the macro to;
Copy the active sheet into a new book and save the file as the current date.
Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.
In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.
I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.
View 14 Replies
View Related
May 3, 2007
I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.
for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.
the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.
i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.
View 9 Replies
View Related
Sep 15, 2014
Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,
sub sending_mail
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet
[Code] ......
View 2 Replies
View Related
Jan 11, 2007
I cannot determine why I am getting an N/A error in Cells I11:L11. The formula is copied and consistent throughout the sheet. The Table referenced in the formula appears to be fine. I just cannot figure this out.
View 5 Replies
View Related
Apr 2, 2014
This macro below is meant to search a sheet for a user selected value, select the entire row and copy it into a new sheet that has been created under the name of the user selected value. It will create said sheet, but isnt finding the value and/or copying the active cell row. Im not getting any errors so it has no issue with the coding itself, it just doesnt work, have i missed something obvious again?
[Code].....
View 2 Replies
View Related
Feb 14, 2014
I have a very basic code to copy "non-blank" data from one sheet and paste to another. The code is not complete yet - I am running in debug mode I get the above error. My code is as below.
Sub SampleFind()
Dim StrWord As String
Dim Quantity As String
Dim i As Long
Call nrows
For j = 2 To 2
For i = 2 To nrows1
[Code] ..........
View 4 Replies
View Related
Oct 10, 2011
I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.
The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.
Code:
Private Sub cmdImportData_Click()
Dim sFName As String
'On Error Resume Next
PrepData
CopyData
FormatColumns
'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
End Sub
View 3 Replies
View Related
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
Mar 27, 2008
I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
View 9 Replies
View Related
Aug 27, 2009
Basically my code is bombing at the red highlighted code. Error: "Run-time error '1004': Copy method of Worksheet class failed"
Sheet "Blank Form" is a hidden sheet. Can I alter the red section of code to work while being a hidden sheet? I tried a few variations but nothing seems to work..
View 5 Replies
View Related
Feb 7, 2007
I am trying to run create a simple macro that copies and paste special values - something I have done 100's of times but for some reason I keep getting an error message - even though I recorded the macro and didnt write it by hand - see below:
Sub Macro6()
Cells.Select
selection.Copy
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
For which I get 'Compile Error - Expected Function or Variable'
View 6 Replies
View Related
Aug 16, 2014
I got an error message that I cannot save while debugging. But I have repeatedly hit the stop icon to stop debugging!
I have minimized all the windows (except the spreadsheet as it does not allow me select it and just beeps) to make sure there are no hidden message boxes.
I do not want to force exit and lose my work but I cannot get out of debug mode for some reason!
View 6 Replies
View Related
Dec 7, 2009
I'm trying to create a macro that will take a worksheet from an Excel workbook, and save it as a text file, with the name determined from user input, and the location being the desktop of the currnet user's computer.
First I use a function, (provided by RoyUK), to get the name of the current desktop, then I try to use that name, along with my user input, so save the file. Obviously, it's not working, or I wouldn't be here. I get a "Run-time error '1004': The file could nto be accessed". I think it's just a syntax issue with the file name, because debug takes me to the "SaveAs" portion of the macro.
View 2 Replies
View Related
Jan 12, 2010
I have got the code below, I am trying to save as file name with it ending with upload, however I am getting run time error 91,
'Worksheet save as upload name
Dim Wk As Workbook
ActiveWorkbook.SaveAs Filename:=FileDrive & Wk.Name & "Uploads" & ".xls", _
FileFormat:=xlNormal
View 9 Replies
View Related
Sep 21, 2006
Here is my existing code...
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application. ScreenUpdating=False
' Cancel user prompted save procedure
Cancel = True
' Code removed... changes some values and settings before saving
ThisWorkbook.Save
' Code removed... return the original values and settings
Application.ScreenUpdating=True
End Sub
For some reason, the workbook is not being saved when the above code is executed. It seems the problem lies with "ThisWorkbook.Save"... in that it just isn't saving! If I delete "Cancel=True", the workbook will save (obviously!).
I have spent an hour trying to work this out and have come to the conclusion that it is something other than my code... has anyone experienced this problem before?
View 9 Replies
View Related
Feb 12, 2014
I am trying to take the template I have created and after the information is entered, if all required is not filled in, it will highlight the cells that need filled in. I get a debug error on
VB:
Cell.Interior.ColorIndex = 6
And the file does not save elsewhere. It goes into never never land. Here is my whole code:
VB:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Start As Boolean
[Code]....
View 5 Replies
View Related
May 12, 2009
I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol
Line using MATCH
On Error GoTo Exiting
where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
With Sheets("Sheet1").Range("1:1")
Set rFound = .Find("Term1")
If Not rFound Is Nothing Then
a = "Term1"
Else
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.
View 9 Replies
View Related
Mar 1, 2007
I need to:Turn off "save" and "save as" command barsPrompt user for a unique filename Save file to a specific directory on the network common to all "p:dataprc"Requery user if filename exists and if they DON'T want to overwrite.Return to worksheet (there is only one) if the user cancels from the message box.Turn commandbars back on The macro runs from an on screen button
I get bug errors on NO or CANCEL when clicked in the message box. This is my current
Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
End Sub
Sub SaveMe()
'
' Keyboard Shortcut: Ctrl+z
'
Dim BaseDir As String
Dim NewName As String
BaseDir = "p:dataprc" ...........
View 7 Replies
View Related
Apr 18, 2009
I have a command button that runs a save macro to which I added cell validation the message box comes up and what I want it to do is go to the cell that is blank
View 2 Replies
View Related
Jan 23, 2010
I am trying to run a macro that will export the 'Results' WS & ask the user where he/she wants the .xls to be saved, though when I click 'Save' nothing happens.
In additons in the save as part it has the WB's name(and full extention) is there a way to make this blank or to have something in it? I.e the WS's name?
View 10 Replies
View Related
Jun 9, 2007
provide me a code for extracting data and outputting it into individual worksheets situated within the excel workbook file. I am wondering what the code would be if the data were extracted to individual text files, individual workbooks or *.prn files?
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Set ws = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ws.Name = wsraw.Cells(i, "D")
With ws
.Range("A1") = wsraw.Cells(i, "D")
.Range("A2").Resize(2) = wsraw.Cells(i, "A")
.Range("B2").Resize(2) = wsraw.Cells(i, "B")
.Range("C2") = 0
.Range("C3") = 1000
.Range("A4") = -999
End With
Next
View 2 Replies
View Related
Mar 30, 2014
I am executing code in a workbook that has been working successfully previously.
Suddenly I get a run time 1004 error in any macro in the workbook that tries to execute a activeworkbook.save command?
View 1 Replies
View Related
Jul 21, 2013
Im struggling to get a workbook to a specific location. I have copied 3 worksheets to a new workbook, which im trying to save to an archive folder. Here is my code.
Code:
Sub atest()
Dim strFileName As String
Dim Archivepath As String
[Code]....
The problem is i keep getting an error (runtime 1004) saying the file could not be found?? well, im trying to create it !..
View 2 Replies
View Related