VB:Thanks,wb1 = ActiveWorkbooks wbt = "C:Tx Monitoring 2011.xls" Application.Workbooks.Open (wbt) wb1.Sheets.Copy After:=Workbooks("Tx Monitoring 2011.xls").Worksheets(Workbooks("Tx Monitoring 2011.xls").Worksheets.Count) [COLOR=red]----> vba tells me there Is an error here. Object required[/COLOR] wb1.Sheets("Transations").UsedRange.Copy Range("A1").Select 'paste the copied data in there Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Workbooks("Tx Monitoring 2011.xls").Activate ActiveWorkbook.Save ActiveWorkbook.CloseIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
With Workbooks("Closed District Lead Summary Jan to Mar 2011.xls") .Sheets(NewSh).Copy _ Before:=Workbooks("K:PROJNexTouch2010 ReportsVersions" & "" & "District Lead Summary " & Format(Now, "yyyy-mm-dd") & ".xls").Sheets(1) End WithAnd this...
Thanks for looking!
By the way, the 'other workbook' is a workbook that is already created, I want to add the one sheet or tab to this already created workbook. I did find some code from Lieth Roth but that code was creating a new workbook. Here's the code I found in this formn...perhaps this can be modified for my needs?
Dim FileSpec As String
Dim Ia As Integer
Dim NewWkb As Workbook
Dim Wksa As Worksheet
Dim WksName As String
With ThisWorkbook.Worksheets(NewSh) '("Replicator")
FileSpec = .Range("A1")
FileSpec = IIf(Right(FileSpec, 1) <> "", FileSpec & "", FileSpec)
FileSpec = FileSpec & .Range("A2")
For Ia = 1 To .Range("A3")
WksName = .Cells(I + 3, "A")
Set Wksa = ThisWorkbook.Worksheets(WksName)
If NewWkb Is Nothing Then
Set NewWkb = ActiveWorkbook
NewWkb.SaveAs fn 'FileSpec
UPDATED - - - I think I found the answer, I record the 'copy' via the normal fashion then added that recored code to the existing code....first test seemed to work well....Sheets(NewSh).Select Sheets(NewSh).Copy After:=Workbooks( _ "District Lead Summary 2011-03-17.xls").Sheets(1)
VB:PLease help! thanks in advanceCommandButton1_Click() Dim WS1 As Worksheet, WS2 As Worksheet Set WS1 = Workbooks("C:UsersDesktopData ExtractionDataDump.xlsm").Sheets("Sheet2") Set WS2 = Workbooks("C:UsersDesktopData ExtractionReport.xlsm").Sheets("Sheet2") WS1.UsedRange.Copy WS2.Cells(Rows.Count, "A").End(xlUp) End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:In my Excel 2007 install the default font size when creating a new workbook is = 8. If I change the default fontsize to be 10 the issue doesn't occur.CopyReportsToNewBook() 'stephen mcintyre May 2011 'copy report worksheets to new workbook in preparation for distribution 'Delete the named range "FinCal" from the new workbook Dim wb As Workbook Dim NewBook As Workbook Dim icountsheets As Integer Dim icounter1 As Integer Application.ScreenUpdating = False 'force saveas format Application.DefaultSaveFormat = xlExcel12 Set wb = ThisWorkbook Set NewBook = Workbooks.Add icountsheets = NewBook.Worksheets.Count wb.Sheets(Array("Trading_Report", "Priority1s", "StoreDownTimeDetailNew", _ "Scorecard_1", "Scorecard_2", "Scorecard_3", "NonScansDetail", "ReinstatedSales", _ "ChangeManagement")).Copy after:=NewBook.Sheets(icountsheets) Application.DisplayAlerts = False 'delete named range not requried in new book NewBook.Names("FinCal").Delete 'delete worksheets created by workbook.add For icounter1 = icountsheets To 1 Step -1 NewBook.Worksheets(icounter1).Delete Next icounter1 Application.DisplayAlerts = True wb.Worksheets("RunSheet").Activate Application.ScreenUpdating = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub UpdateAllWorkbooks() Dim MyFolder As String Dim SrcWks As Worksheet Dim Wkb As Workbook Dim WkbName As String Set SrcWks = ThisWorkbook.Worksheets("Data Overview") MyFolder = "C:Asia" WkbName = Dir(MyFolder & "*.xlsx") Do While WkbName <> "" Set Wkb = Workbooks.Open(WkbName) SrcWks.Copy Before:=Wkb.Worksheets("Sheet1") Wkb.Close SaveChanges:=True WkbName = Dir() Loop End SubThanks in advance
VB:So most of it is fine but I would like the copied worksheet to be placed at the end of all the worksheets already in the Filename_Sample file. The Filename_Sample workbook will be my master, so it will have a growing number of worksheets as I do this process. I don't know how to do this and the macro recorder is not useful for this either.Sub Copy_Worksheet() ActiveSheet.Select ActiveSheet.Copy After:=Workbooks("Filename_Sample.xlsx").Sheets(1) End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines