The problem sheet attached to this thread. I would like to extract all rows belonging to one person to another sheet named after the person and also their status.
For instance, I want a VB script which extracts all rows with Mark when his status is A/V to another sheet named as Mark and likewise for Steve.
I need some code that will keep one row, delete 5 or 6 rows keep one row and repeat the process over and over again until the end of the data.
like this:-
1 keep row
2 delete row
3 delete row
4 delete row
5 delete row
6 keep row
7 delete row
8 delete row
and on, and on......
I have a calendar-template that contains entries - little text-chunks - and i also have a excel-sheet that contains 365 texts - those have to get imported into the Calendar-template. - see the attached files for more infos. Play around with the calendar-template.
Note: the attached data contain the following
a. the calendar-template (attachment a)
b. the excel-sheet - with the 365 text-chunks that have to get imported into the template (attachment b)
What is aimed - i want to have a weekly calendar for my own usage
To see what we already have - take the calendar-template.
You see the calendar-template contains the fully-fledged view of the second week in 2014.
If you jump to the filed A 2 in the calendar-template (attachment a) - here you see the date - actually the 2014-01-06
You can play around and see that the greek verses are already included for the whole year. If you choose any date of the year - the greek verses in the template already are added.
What is missing - are the additional text-chunks that are represented in the excel sheet.(b)
note. i have colored the fields in the calendar-template and the excel sheet with corresponding colors so we can see - which fileds have to get filled with which text-chunks of the excel-sheet. Additionally the calendar-template contains the fully-fledged view of the second week in 2014.
Question. can we do a automated version?
Attached files:
a. _calendar_2014_week_2_demo_excel_version - this is the calendar template
b. _words_2014_365_days_excel_version - this is the additional text sheet that contains the 365 text chunks - slogans
I am using an autofilter to delete chunks on unwanted data, by using Range(Selection, Selection.End(xlUp)).Select however it picks up the header row. Is there anyway I can either get the selection of data to select one less line, or a way to get it to leave the header line?
View 2 Replies View RelatedI 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???
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 RelatedI'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..........................
i am trying to copy a data from another sheet, and i know to use "=sheetN!XM" but i have already renamed my other sheets, so i cant copy data from renamed cell using this command also by changing the command as the new sheet name...
View 3 Replies View RelatedI've got a spreadsheet made up of a Document Map and 100 sheets. I'd like to copy each sheet to its own file while retaining the name of each sheet, but I can't figure out how to retain the name- I get "Book 1" instead.
View 7 Replies View Relatedi have 2 columns of data in multiple worksheets which i wish to copy into one column on another sheet.
i want to be able to click a button and have all the data compile with the item number and serial number into sheet "compile" no matter how many sheets i have (my current macro copys the sheets with a sheet prefix onto it, ive left 31 and 32 there) automatically. Ive edited compile with one sheet of data (note i just copied 31 to 32 - normally all data would be different, not a straight copy) to show what i am trying to achieve. the 17000 will stay the same no matter what, so i just used an IF function there. Basically the part im having issues understanding is how to copy data to the next blank space in the work book, as it wont always be full of data.
At the end of getting this part working i will set it up to automatically compile into a seperate work book as a csv comma delimited to upload to a database for stocktaking purposes - but i just want to get the basic copying working first.
I have two spreadsheets in the same workbook. Sheet1 contains statistics for teams and years. Sheet2 contains a listing of the teams in different orders.
How can I have excel copy a teams stats from Sheet1 to Sheet2 as it goes down the row? For example, A1 is atlanta 1996 so it copies a section of atlanta's 1996 stats to a specific range. Then A2 is detroit 2002 so it copies detroit's 2002 stats, and so on and so forth all the way down the list?
I am a beginner with VBA but I have experience with C/C++. I am just not sure of the functions for excel do all these things automatically and am having trouble finding applicable examples.
Given:
1.Four similar data worksheets (W1, W2, W3, W4).
2.Each worksheet has the same headings in row 1.
3.The data for each worksheet begins in column 2.
4.Each worksheet has an arbitrary different number of rows.
Problem:
What code allows you to automatically place the contents of all four data worksheets (W1, W2, W3, W4) into a different worksheet (w5) with only one common heading?
I have 12 workbooks with 2 spreadsheets in each: Assessments and Instructions. What I would like to do is have one master spreadsheet of instructions I can use to update all the other sheets. After changes are made, I would like to run a macro that would copy the master instructions sheet, open the other workbooks, copy over the information in those workbooks (on the Instructions sheet only), and then close them. I have no idea where to start.
View 9 Replies View RelatedBasically, what I am trying to do is my workbook has 12 sheets, 1 for each month. In cell A7 on the first sheet (Jan) I would like to enter 01-01-2009 and then it add a month on each of the sheets. e.g. 01-02-2009 01-03-2009 and so forth. I know I can manually put in the first of the month on each of the sheets, just wondering if it can be automated.
View 3 Replies View RelatedI have a workbook with large number of sheets (150+). All sheets have the same column structure (same number of columns with same heading in Row 1), but the sheets vary in the number of rows.
I am looking for a VBA (Macro) that copy (combine) the same range (d2:g6 from each sheet) into a new sheet stacked.
Why my code is not working. When I choose a single column it works. Once I select more than one column it doesn't work. It something to do with my "column1:column2" reference.
View 2 Replies View RelatedIm looking for a way to copy all rows with data in them from row 3 to the last row with data to another worksheet. I would like to copy the data from ALL worksheets in the workbook apart from one called Grade Boundaries.
All the sheets have the same layout. I simply want to produce a sheet with the data from all sheets in one place.
I have an excel worksheet with TableNames and ColumnNames. I would like to copy the different TableNames to it own sheet with the corresponding ColumnNames. I also would like the sheet to be renamed to the tablename. I have the workbook but I don't know how to attach it.
View 4 Replies View RelatedThis should be a fairly simple question, I've already got the code for copying sorted, I'm just curious about several of the named ranges within the sheet and what happens to them when I copy it all over.
I'm working from a spreadsheet made by my predecessor and they've got a template sheet with numerous named rages all scoped to the workbook, on the new template I've created, it has lots of ranges scoped to the sheet itself. The new template works just fine as it is but I just want to try and make sure that when I change over the templates it will continue to work fine and the current template's named ranges are going, so far I have actually copied everything and are running tests, but you can't test for everything, so if the named ranges on the new template being only scoped to the sheet will affect it at all?
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
I have a mastersheet with all the information and I want to be able to copy it to new sheets.
The four different sheets I want are: Male-Olympic, Male-Sprint, Women-Olympic, Women-Sprint. Is there anyway to go through the sheet, copy the rows, and paste them into their proper sheet.
I have a large workbook which has about 15 sheets. About half of these are template sheets which are copied multiple times within this workbook. The workbook is very large and can't be posted. I would like to hear from anyone who can give me any direction to look in given the following symptoms. The problem is that VBA code stops executing and gives a 1004 Error. *The problem is stable and reproduceable.
*It occurs exactly the same across three different computers with varying versions of Excel and memory.
*The problem is persistant beyond VBA. By that I mean if I stop the debugger and try to manually copy the active sheet, the screen flashes but the sheet is not copied. Therefore I think the 1004 error is a symptom rather than a cause
*From scratch, I get to copy 43 times before I get the error.
*If I then save the workbook and re-open (don't need to close Excel) I then get to copy another 27 sheets.................................
I'm not great anyways with VBA Macro.Effectively, here is my issue. I have a spreadsheet which is really badly designed.In one column I have multiple numbers separated by a comma. I need this data separated into new individual rows, but at the same time, copying the data in the other columns in that row to the new row.
Example of what I have:
Test 1 54 email1
Test 2 32, 343, 63, 34 email2
Test 3 4934, 5342 email 3
What I need:
Test 1 54 email1
Test 2 32 email2
Test 2 343 email2
Test 2 63 email2
Test 2 34 email2
Test 3 4934 email3
Test 3 5342 email3
I have have the following code below which paste everything into a new column and into a new row, but the problem I have is that it does not push the other rows data down, nor does it copy the 1st rows data (for that data set) into the new rows created.
Code:
Option Explicit
Sub Macro1()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
[Code] ........
The amount of rows I have, so this manually (text to column, then transcoding etc) it out of the questions (i have 1000 rows of this!)
I have a data sheet that I enter all data into. I would like to divide the data into different sheets depending on the month the job was entered. Please see a sample I have attached. I have tried to convert the month (colE) into a figure (colF). I am hoping the data automatically copies across into the correct month sheet or perhaps I can push a button and it will do it for me. Data will get entered on a daily basis by staff but only onto the main sheet, this will then by some miracle be duplicated into the corresponding sheet without loosing any data on the main sheet.
View 8 Replies View RelatedThere are two sheets: Sheet1 and Sheet2
Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)
Columns in Sheet 2 are the following:
company PERMNO number, ticker, dates, market capitalization
In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.
I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.
In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.
Example:
Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803
3. 13123 199904
4. 65456 200005
5. 44550 200104
6. 44550 200211......
I have a work sheet with about 35,000 lines of data. Every day I have to sort the list by product category (I use auto filter to separate) and then copy the results into a new worksheet, there are about 300 product catagories. I'm new to macros but I'm wondering if there is a macro that can automate this process.
View 6 Replies View RelatedIf the name in Column F of "Sheet1" is found in "DataPEs" in Column B, then copy the block of 6 cells of data below it in column Q to column D of "Sheet1" in their relative rows. Additionally, if column Q has data then copy it, if not then copy the data only in column M. Finally, the number of matching names will vary in Sheet1 but will stay mostly the same in the DataPEs sheet. I've highlighted one block of data in green as an example.
View 13 Replies View RelatedI'm working in one workbook with 2 sheets.
sheet 1 have simple values, for example
cell A1:5
Cell A3: 10
Cell A5:15
Cell A7:20
Cell A9:25
Cell A11:30
Cell A13:35
Cell A15:40
In sheet 2 i have the following formulas:
Sheet 2 Cell A1: =Sheet1!A1+Sheet1!A3
Sheet 2 Cell A2: =Sheet1!A5+Sheet1!A7
I need to respect this sequence when copying these 2 formulas in the same sheet 2 cell A3, however when I do that I get the following formula: =Sheet1!A3+Sheet1!A5. While i need it to sum up A9 and A11. i.e respect the order of the first 2 formulas.
I want the following script to:
1) Find and define a range ("DateRng") based on cell values. This is controlled by the first block and the script does this job correctly.
2) Find cells
There is a Microsoft VBA bug currently outstanding where if your VBA replicates a sheet too many times, it throws a run-time error. I read MS' statement that, to get around this problem, what one can do is to save and close the file, and re-open the file once in a while.
Doing this certainly helped, where it used to crash after copying only a few times and now it goes on until 40 ~ 50 times. But it still crashes. Has anybody been able to get around this problem? Currently I am having VBA save, close and re-open the file every 7 or 8 times or so...
