Copying Sheets Into A New Book Macro
Oct 6, 2009
I'm having an issue with a macro that copies sheets from one workbook into a new workbook. The issue is it doesn't copy all the sheets. It only copies the first 10 and doesn't capture the the remaining 15. I've checked the range of the translation table and the issue doesn't appear to be there.
Sub SBGFiles()
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim a As Worksheet
Set a = Sheets("Input")
Dim Fpath As String
Fpath = ThisWorkbook.Path & ""
Dim SBG As Range
Dim RU As Range
Run ("UnPro")
For Each SBG In a.Range("B44:D44")
Workbooks.Add
newbook = Workbooks.Count
Workbooks.Item(newbook - 1).Activate
On Error Resume Next
View 9 Replies
ADVERTISEMENT
Jul 18, 2014
I made the following code to merge 2 workbooks together.
The code is to be executed when the user has Workbook A opened. (All sheets in workbook KPISWD are supposed to be moved after all worksheets in workbook KPICustomers).
I keep getting a debug error on the code that is supposed to do the actual move and loop until it is finished with all of the sheets in Workbook B.
Code:
Dim KPICustomers, KPISWD As String
KPICustomers = ActiveWorkbook.Name
Workbooks.Open Filename:= _
"W:FacturatieKPI per periode SWD.xls"
KPISWD = ActiveWorkbook.Name
[Code] ..........
View 3 Replies
View Related
Aug 29, 2007
i have done a search on this topic and found many similar answers to many similar questions. All specify using Application.Run "workbookname.xls!macroname".
In my code, the filename has an assigned value as the master code goes out to many secondary files - the user selects the particular one they want at the start of the macro. The variable assigned is called "UpdateKAMsFile".
how do i get the macro called KAMsRandomColour to run in the workbook designated by UpdateKAMsFile?
View 9 Replies
View Related
Apr 22, 2014
I need to transfer data from one sheet to another sheet in another workbook. The output row corresponds to a value in input column a, and output column corresponds to a date in input sheet column B.
I've previously dim-ed the input/out workbooks/sheets as wbin, wbout, sheetin, sheetout respectively. The error I get is runtime error '9': subscript out of range in the copy destination line.
Code:
Windows(wbin).Activate
Sheets(sheetin).Select
iMaxRow = 5000
Dim subj1 As String
Dim subj2 As String
For iRow = 1 To iMaxRow
[Code] .........
For now, i've disabled the on error resume next. Also, the input column a has 4 numbers followed by string, while the corresponding output row header only has the 4 numbers, hence I tried matching with the wildcard.
View 9 Replies
View Related
Aug 13, 2012
How to copy a formula (16 columns) from a workbook to another, without referencing the source workbook.
Some people are using tricks such as replacing the equal sign "=" for another character such as "^" then using replace all to put the equal sign back again... but this is too much trouble.
View 2 Replies
View Related
Jul 16, 2014
so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.
2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.
View 6 Replies
View Related
Jul 27, 2012
I have eight sheets that have lists of questions in, which I want to copy across to a results sheet if the answer to a question is 'Yes'. Each question takes up rows B:H inclusive, and I would want to copy them to rows B:H in the results sheet. The "Yes" value will be found in column F of each row.
How can I set up a macro to copy the entire rows (without formatting) into a results sheet properly? I've tried every solution I can find but always hit a roadblock somewhere.
Ideally I would like to have a 'populate' button on the results sheet that would find every question that was answered 'yes' across the eight survey sheets and import them into results sheet.
View 2 Replies
View Related
Nov 9, 2006
I am moving some sheets into a new workbook and from the new workbook I want to prompt the user to save the workbook where they see fit. For some reason I cannot seem to save the new workbook only the old with the below code.
Sub Moving
Dim wb As Workbook
Set wb = ThisWorkbook
Sheets( Array("Hk", "Li", _
"SAM")).Select
Sheets("SAM").Activate
Sheets(Array("Hk", "Li", _
"SAM")).Copy
If wb.Saved = False Then
Select Case MsgBox("Do you want to save your changes?", vbYesNo Or vbExclamation Or vbDefaultButton1, "J & R Solutions")
Case vbYes
wb.Close True
Case vbNo
wb.Close False
End Select
End If
View 2 Replies
View Related
Mar 19, 2005
got work book that has about 20 sheets in it.....there are links throught the workbook. ......one sheet in particular when i go to it, it freezes up...and i get the dreaded "Send Error Report to Microsoft" window"...and we all know what happens next.....excel shuts right down.
I can open the workbook and click on ANY sheet....but when i click on this one particular sheet in the book i get the error message
This work book is a template....its the estimating work book i use at work...and whenever i get a new job to estimate i open up this template.....put in my data and save the workbook as the job title.....so basically i have about 50 of these workbooks......and it doenst matter which one i open.......whenever i go to this one same sheet in any workbook i get the freeze
and the freaky thing about this is it does it randomly....I make save this template (as a new work book) when i get a new job to estimate.....and i never have any problems.......but today for some crazy reason its doing what i mentioned above......it did this about a month ago too.......same exact problem.
when it happened the first time i tried all kinds of stuff, and the only thing that worked was i had to go to windows explore...save all the files to a memory chip.......go to a different pc with my version of windows (XP)....open each file up......re-save it.......go back to my pc...reload it
View 9 Replies
View Related
Feb 11, 2010
I've been trying to use the below code,
Sub combine()
'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)...
View 9 Replies
View Related
Jan 27, 2014
spread sheet training matrix. The subjects to be trained on are plentiful so I have broken these down into different sheets. I produced this to my boss and the first thing he asked was "can we link the sheets so I can get information about one single employee" in other words I have a list of employees in the first column and this list is the same on each sheet, the headings on the sheets are different but instead of having a set of columns about forty headings wide I have split them down to ten headings on four sheets. The big question! Is it possible to print one sheet with one employee but all of the headings? When I say headings I mean the columns under the headings as well.
View 4 Replies
View Related
Feb 1, 2007
i want to do a macro, that will copy and transfer Selected worksheets to a new Workbook? so guessing, you select the sheets, then click the button and it copies then opens new blank workbook and puts them in?
View 9 Replies
View Related
Jun 9, 2007
I want to create a form and use VBA to make a combo box list all the sheets in a book. Is it possible to select multiple entries like in HTML? I would like the user to be able to select the sheets they do not want to delete before a macro runs.
View 2 Replies
View Related
Nov 16, 2009
I'm trying to copy rows from one workbook that is exported from access to multiple worksheets in multiple workbooks. I used a macro I found here from JBeaucaire that will copy the data for me but it over writes the information I have in rows 1-3 and also the rows below, 28-35.
Is there a way to copy the data without loosing the information in the first three rows and the rows below where the data goes?
View 6 Replies
View Related
Jun 5, 2007
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).
I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:
I want to insert a column after column G.
I would like to enter the following formula into H2:
=IF(G4="u", "u", IF(G4
View 9 Replies
View Related
Aug 26, 2012
I'm trying to write a macro that selects cell A1 in each sheet within the book. I've written the following code:
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
However, it produce this message: "Run-time error '1004': Select method of Range class failed."
If I slightly modify the code to
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Select
Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
It works fine. I'm curious why this happens.
View 6 Replies
View Related
Jan 6, 2009
There is an autofilter applied to worksheet A, I would like to transfer only the autofiltered range from worksheet A to worksheet B, a clear worksheet B statement would also help.
View 9 Replies
View Related
Jun 22, 2007
I have the below code, which now looks to see if a file is open or not, if it is, then copy and past 'Data' and if not open the book and copy 'Data'.
I think the code is sort of right, but im missing something, as i keep getting runtime error when i try and copy. Here is the
Sub PrintSaveKPIUpdate()
Dim sFilName As String
sFilName = "C: estCashSales_KPI.xls"
Set Main = ThisWorkbook
If IsOpen(sFilName) Then
' Book is Open.
Worksheets("Setup Data"). Range("Data").Activate
View 9 Replies
View Related
Feb 22, 2013
I have merged 336 individual spreadsheets into one book, now I want to merge the data in all the sheets into 1 individual sheet. All the sheets have the same size and range, I need to copy a constant range(row,column) from all the different sheets into one.
What VBA functions to use???
View 4 Replies
View Related
May 25, 2014
i wanna copy C and D columns in Sheet1 and insert these 2 columns (copy + insert copy cells function, not copy + paste) to all other sheets except Infopage sheet
View 14 Replies
View Related
Oct 7, 2007
I have a macro that I would like to run on every worksheet in my book (over 100 sheets wide). As it takes about 20 secs to run on each sheet, and I don't want to click from sheet to sheet, how can I make it run on every sheet in the book? And no, they aren't named sequentially, either alphabetically or numerically.
is there a function in Excel to return the number of sheets in a book?
View 9 Replies
View Related
Dec 22, 2009
I need a macro that will copy specific columns from one book into another workbook.
I imagine this isn't too hard, but i can't seem to find the answer.
Also, i have another macro at the moment which allows me, when i run it, to select the workbook which i wish to copy the columns from.
View 9 Replies
View Related
Nov 25, 2013
I have a macro that used to create a new book for each of the worksheets in a workbook that would name them the same as the worksheet.
Since our company upgraded to Windows 7 the macro, more often than not, fails to work.
The code for the macro is
Code:
Sub NewBook1()
'
Set wbSource = ActiveWorkbook
For Each ws In wbSource.Sheets
ws.Copy
Set wbDest = ActiveWorkbook
wbDest.SaveAs strSavePath & filePrefix & " " & ws.Name & " " & fileSuffix
wbDest.Close
Next
Application.ScreenUpdating = True
'
End Sub
View 5 Replies
View Related
Feb 28, 2009
I have several similar documents open. They each have a macro with the shortcut CTRL + SHFT + T. The problem is each macro is different for each book. When I am working on book X, I don't want macro from book Z to run, but it does.
I need all of them to be open.
View 9 Replies
View Related
Apr 2, 2007
I need to get this macro to process the cells for every worksheet in a book rather than just the active one
Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Next
Application.ScreenUpdating = True
End Sub
View 9 Replies
View Related
Sep 6, 2008
At job, Mac OSX and Address Book 4.0.6 with contact info including in many cases emails. I'd like to get it of there, into Excel to manipulate, and ultimately into a FileWrecker Pro database. How to export 1087 entries from Address Book including only selective data fields is the question, and I know that's "slightly" OT, but I do want to import into Excel, so I hope that vindicates me! If not, and you can point me to a good MacForum, I'll settle.
View 9 Replies
View Related
Mar 17, 2009
Is there a way to run a macro automaticly on a closed work book every Friday ? I've found ways of doing this using on-open event but It made me wonder if this was possible.
View 9 Replies
View Related
May 20, 2013
Macro to highlight a cell A1 every monday ( or any day) when we open the book for the first time.
Macro should not run when we open the book from the next time till that week gets over and again it should run once the next week started.
View 1 Replies
View Related
Aug 20, 2009
I would like to know if there is a way to copy a spreadsheet and paste it into a new tab on another spreadsheet. We currently download 2 lots of spreadsheets from SAP and would like to create another spreadsheet to act as a 'central' preadsheet. What I would like to happen is that the data from the 2 downloaded spreadsheets will be automatically pasted into 2 new tabs on the central spreadsheet, then run some formatting macros (which I can do fine).
View 3 Replies
View Related
May 6, 2008
I'm strugling to write some VBA code, it's a bit untidy at present but it works untill I try to import values from sheet 2 to sheet1, I get an object error, but it dosen't mean anything to me.
Basically this code looks at sheet2 if the data is the same it overwrites it with upto date data, else it will add new unique enteries
to the last row.
Sub Importer()
Dim y As Integer
Dim z As Integer
Dim R As Long
'database
Sheets("Sheet1").Select
Cells(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
R = Selection.Rows.Count
'daily data
Sheets("Sheet2").Select
Cells(1, 1).Select
Dim Amax As Integer..........................
View 9 Replies
View Related
