Small Excel Question Not Worth Its Own Thread

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »

ZerodotJander: Dec 29, 2004; Chinaman, explain!

This looks like kind of job Google Refine was built for - http://code.google.com/p/google-refine/

If you can't find a good way to automate it in Excel, this tool should make manually cleaning the data much easier compared to doing it directly in Excel.

# ? Jun 21, 2012 22:21

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 16:12

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I'm not sure how I lived before I knew Google Refine existed.

# ? Jun 22, 2012 01:33

Brennan: Jan 18, 2005

I tried to make a macro yesterday that would find a string by looking at a column on one worksheet and if it found it would copy and paste it to another worksheet. It works fine for the original column I set it up for, but if I modify it by changing x to x = 7 so it looks at column G instead of E it won't work unless the row number in line 4 is set so the second number matches the x value. The problem is it then skips all rows above 7 so if there is a match above it it doesn't get copy and pasted. If that number on line 4 doesn't match x the macro doesn't work. I'm confused as to why it is behaving like this.

code:

Sub sherri()
x = 5
Do While Cells(x, 6) <> ""
If Cells(x, 5) = "sherri" Then
Worksheets("All Trouble").Rows(x).Copy
Worksheets("test").Activate
erow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("test").Rows(erow)
End If
Worksheets("All Trouble").Activate
x = x + 1
Loop

End Sub

# ? Jun 22, 2012 15:03

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Brennan posted:

I tried to make a macro yesterday that would find a string by looking at a column on one worksheet and if it found it would copy and paste it to another worksheet. It works fine for the original column I set it up for, but if I modify it by changing x to x = 7 so it looks at column G instead of E it won't work unless the row number in line 4 is set so the second number matches the x value. The problem is it then skips all rows above 7 so if there is a match above it it doesn't get copy and pasted. If that number on line 4 doesn't match x the macro doesn't work. I'm confused as to why it is behaving like this.
code:
Sub sherri()
x = 5
Do While Cells(x, 6) <> ""
If Cells(x, 5) = "sherri" Then
Worksheets("All Trouble").Rows(x).Copy
Worksheets("test").Activate
erow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("test").Rows(erow)
End If
Worksheets("All Trouble").Activate
x = x + 1
Loop

End Sub

Cells(x,6) looks at row x in the 6th column (F). So when you change x=7 you are looking at cell F7 and not E6 as you intended. So if F7 is blank the Do loop never triggers.

# ? Jun 22, 2012 15:34

Brennan: Jan 18, 2005

I apologize in advance, this is my first attempt at anything with code. I'm confused because when I change it to x = 7 and line 4 to

If Cells(x, 7) = "newstring" Then

it seems to start at G7 not F7. It will copy/paste the row if the string in G7 matches, like it will copy/paste the row if E6 matches in the original code. I've also seen conflict. I found this odd yesterday because everything I read said Cells should be (row, column) but it seemed to be reversed. Would it be better to move to two variables and just change the variables as needed? I tried to use Range first and I was getting errors which is why I switched to Cells.

# ? Jun 22, 2012 17:03

Stealthgerbil: Dec 16, 2004

Does anyone know a way to count cells that don't contain a value of zero and are not in a range? I need to count the number of cells but I can only find ways that work if it is a range and not just a bunch of cells.

# ? Jun 24, 2012 20:44

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

By range do you mean named range (e.g. 'results') or cell range (e.g. A1:A500)? If the latter, unfortunately COUNTIF only accepts ranges (or one individual cell) as an argument. You can kind of get around it though if you can clearly identify the cells you don't want counted:
http://www.pcreview.co.uk/forums/using-countif-specific-cells-t2532815.html

What they're basically doing is one COUNTIF range subtracted from the other; I'm not sure if this would help you, or if you just need to be able to arbitrarily count specific cells (e.g. COUNTIF(A2 Q11 Z23,">0")).

# ? Jun 26, 2012 23:57

uG: Apr 23, 2003; by Ralp

I have a column that has a number used for an identifier. An example might look like:

code:

123 Mike
123 John
123 Jill
124 Nick
124 Beth
125 Tyrone

Every time the identifier changes, I want to insert a blank row so I can more easily identify the groups. i.e:

code:

123 Mike
123 John
123 Jill

124 Nick
124 Beth

125 Tyrone

Is there a way to do this with conditional formatting, or do I need to learn/write a macro?

# ? Jun 27, 2012 19:36

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

uG posted:

Is there a way to do this with conditional formatting, or do I need to learn/write a macro?

The macro is pretty short so I just whipped one up, seems to work for your data set and some more that I threw in to test it.

code:

Sub splitByID()
    
    IDCol = 1
    lastrow = Cells(Rows.Count, IDCol).End(xlUp).Row
    tempID = Cells(lastrow, IDCol)
    
    For i = lastrow To 1 Step -1
        If Cells(i, IDCol) <> tempID Then
            tempID = Cells(i, IDCol)
            Rows(i + 1).Insert
        End If
    Next i

End Sub

You can set IDCol = something other than 1, but this should split it up. edit: removed some superfluous conditionals for the start.

DukAmok fucked around with this message at 19:58 on Jun 27, 2012

# ? Jun 27, 2012 19:55

ZerodotJander: Dec 29, 2004; Chinaman, explain!

Just add extra rows to the end, 1 for each identifier, with no name. Then sort by the numbers to insert the extra rows where you want them. Filter your second column to blanks to delete the values in column 1, and you're done.

Edit for clarity-

Do this:

code:

123 Mike
123 John
123 Jill
124 Nick
124 Beth
125 Tyrone
123
124
125

Sort by column 1, then filter by column 2 to easily remove the values you don't want.

ZerodotJander fucked around with this message at 21:43 on Jun 27, 2012

# ? Jun 27, 2012 21:40

Lawnie: Sep 6, 2006; That is my helmet
Give it back
you are a lion
^{It doesn't even fit}; Grimey Drawer

Does anyone know of a good tutorial for solving polynomial systems of equations in excel? I have 2 unknowns in 2 third degree polynomial equations. Ideally I would set up the sheet to have a few input values that go into the equations, and it could simply spit out the results, which I could then do some other basic calculations on to show useful information.

# ? Jul 5, 2012 19:46

Nickelodeon Household: Apr 11, 2010; I like chocolate MIIIILK

So I have this spreadsheet of all property sales for NJ for the years 2002-2011 (let's call it "master"). In a second spreadsheet, I have observations of interest culled from the master sheet (let's call it "observations"). What I need to do is figure out a function that will search the master spreadsheet for instances where the text in columns B (seller's name) and E (buyer's name) and the date of sale in column Q matches in both tables and return the entire matching row (that is, the data in columns A to Z) to a third spreadsheet.

Secondly, I need to write a function that will do the same as above only it needs to pull values only if the text in column L is "3B".

To the first function can I do something like

code:

=vlookup(B1&E1&Q1, master table range, column number, false)

Or would something like this be better

code:

=sumproduct((master!B:B= observations!b1)*(master!E:E=observations!e1)*(master!Q:Q=observations!q1)*master!a:a)

with one of these for each column?

What if there's 4 or 5 matches in the matches for a given set of values in my observation table? Will this affect my results (that is, will it return each of the matches or only the first)?

Am I better off building a pivot and doing a getpivot function (and if so, any pointers here would be welcome as I don't have a lot of experience with pivot functions)?

# ? Jul 6, 2012 14:27

Sonic H: Dec 8, 2004; Me love you long time

Is there a way of restricting values entered into a cell to be only numeric? Data Validation only seems to allow this for numbers within a certain limit - I don't want to limit the numbers entered, just the data type. Essentially, the limit is +/- Infinity, but I don't see an easy way of doing that without VBA (which I'm not allowed to use in this WB).

I thought about the "Custom" Data Validation option using If(ISNUM({CellAddress}),TRUE,FALSE) but that doesn't seem to allow anything (in that it chucks up a "this is not allowed" notification for any entered value, numeric or otherwise) plus it's not dynamic so I'd have to make a formula for a huge range of cells

Edit: I guess what I'm really asking is: "Is there a way of doing it without setting the limits to crazy high/low numbers like +999999999999/-999999999999999 ?

Sonic H fucked around with this message at 14:08 on Jul 13, 2012

# ? Jul 13, 2012 14:03

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

I doubt this is possible, but figured I would ask as someone might know.

I'm trying to build a spreadsheet to budget per month what I take in and what I expense out. Right now I post my paycheck each time I receive one, and I post up what dates and the amount of when my expenses would be charged to my bank account for auto payments.

I'm wondering if it was at all possible to pull the date/amount due from the sites automatically when they update so I can see without having to login each time?

# ? Jul 13, 2012 18:03

G-Dub: Dec 28, 2004; The Gonz

You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls.

You have two methods of doing this - one is having your script mimic user inputs in to form fields and the other is doing direct POST calls to the relevant pages. For online banks I imagine their site security will mean option 1 is the only feasible one. I can try and help you out but there is quite a bit involved.

# ? Jul 14, 2012 19:04

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

G-Dub posted:

You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls.

You have two methods of doing this - one is having your script mimic user inputs in to form fields and the other is doing direct POST calls to the relevant pages. For online banks I imagine their site security will mean option 1 is the only feasible one. I can try and help you out but there is quite a bit involved.

Unfortunately I'm a beginner, so this might be too much trouble than it's actually worth. There is no problem checking all the time what the due date is and such, but I figured this would just be easier.

# ? Jul 15, 2012 00:20

stuxracer: May 4, 2006

Sonic H posted:

Is there a way of restricting values entered into a cell to be only numeric? Data Validation only seems to allow this for numbers within a certain limit - I don't want to limit the numbers entered, just the data type. Essentially, the limit is +/- Infinity, but I don't see an easy way of doing that without VBA (which I'm not allowed to use in this WB).

I thought about the "Custom" Data Validation option using If(ISNUM({CellAddress}),TRUE,FALSE) but that doesn't seem to allow anything (in that it chucks up a "this is not allowed" notification for any entered value, numeric or otherwise) plus it's not dynamic so I'd have to make a formula for a huge range of cells

Edit: I guess what I'm really asking is: "Is there a way of doing it without setting the limits to crazy high/low numbers like +999999999999/-999999999999999 ?

You can do this with a customer data validation.
Edit: No clue if this worked outside of Excel 2010

Highlight cell.
Data Validation > Custom
forumla is: IsNumber(cell) e.g. IsNumber(B2)

Copy the cell
Paste Special > Validation

# ? Jul 16, 2012 16:02

Bulls Hit: Dec 30, 2011; Legit questions always get stupid responses. Perhaps your brain will grow and you will gain intelligence and you might be able to provide an intelligent response someday! I don't pray, so I'm just going to hope you do get that intelligent brain some day.

G-Dub posted:

You can create IE objects and have them do all your grabbing. It will be difficult if you are a beginner. The reference you want is Microsoft Internet Controls.

You have two methods of doing this - one is having your script mimic user inputs in to form fields and the other is doing direct POST calls to the relevant pages. For online banks I imagine their site security will mean option 1 is the only feasible one. I can try and help you out but there is quite a bit involved.

I did a search for Microsoft Internet Controls, and can't really find exactly what to do. Is there a tutorial page that might walk me through what to do that you know of?

# ? Jul 16, 2012 17:27

G-Dub: Dec 28, 2004; The Gonz

I haven't seen any tutorials. I learned how to use the IE object by instancing one and loving around.

Start off with the following (not written in the editor so sorry if it doesn't compile) and have a play around with the different IE methods. Remember to reference Microsoft Internet Controls

code:

Public Sub Test()

Dim objIE as New InternetExplorer

objIE.Visible = True ' Normally you would have this as false but this lets you watch it work

objIE.Navigate "http://forums.somethingawful.com"

End Sub

This seems to cover some form stuff, which you will need for logins

# ? Jul 16, 2012 20:14

Olewithmilk: Jun 30, 2006; What?

Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this:

The data in the column in green are the names of different genes. The data in the red row are the annotations that match the individual genes so I know what they are and what they do. In the Excel sheet I've got there are 400,000 of those rows. I've put this data through another program to give me some genes of interest and its given me about 350 names but that is separated from those annotations. Is there anyway I can find those 350 genes in my huge excel sheet automatically and extract their individual rows all at the same time without having to manually "ctrl-f" 350 times for each individual interesting gene and copy and paste each one myself? Sorry if I'm not explaining this very well!

# ? Jul 16, 2012 22:18

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Olewithmilk posted:

Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this:

The data in the column in green are the names of different genes. The data in the red row are the annotations that match the individual genes so I know what they are and what they do. In the Excel sheet I've got there are 400,000 of those rows. I've put this data through another program to give me some genes of interest and its given me about 350 names but that is separated from those annotations. Is there anyway I can find those 350 genes in my huge excel sheet automatically and extract their individual rows all at the same time without having to manually "ctrl-f" 350 times for each individual interesting gene and copy and paste each one myself? Sorry if I'm not explaining this very well!

Two quick ways:

1. Add another sheet, and put in a column of your 350 gene names, and use VLookups in the columns to the right of it to pull out the useful attribute value into those columns. So if "15071_c0_seq1" was in A2, you'd have B2 as "=vlookup(A2,Sheet1!A:M,4,False)" or something similar, and copy that across to C2-M2 or however many attributes you want to pull out.

or

2. Add another sheet, put in the column of your 350 gene names. Add another column to your original sheet, and in it put a vlookup across that second sheet column of names. So like, "=vlookup(A2,Sheet2!A:A,1,False)", which will just return itself if it finds it, or an error if not. Then just filter or sort by that extra column on your original data set.

DukAmok fucked around with this message at 23:17 on Jul 16, 2012

# ? Jul 16, 2012 23:14

Old James: Nov 20, 2003; Wait a sec. I don't know an Old James!

Olewithmilk posted:

Hello! I asked this in completely the wrong thread before so hopefully it will go down better here. I have some data arranged like this:

The data in the column in green are the names of different genes. The data in the red row are the annotations that match the individual genes so I know what they are and what they do. In the Excel sheet I've got there are 400,000 of those rows. I've put this data through another program to give me some genes of interest and its given me about 350 names but that is separated from those annotations. Is there anyway I can find those 350 genes in my huge excel sheet automatically and extract their individual rows all at the same time without having to manually "ctrl-f" 350 times for each individual interesting gene and copy and paste each one myself? Sorry if I'm not explaining this very well!

Good timing. This afternoon I wrote a macro for a coworker that basically does the same thing you want.

code:

Sub AnnotationMatch()
    'Turn off calculation, screenupdating, and alerts to improve performance
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False
    On Error GoTo Err1
        
    'Declare variables
    Dim strGENE As String
    Dim wshOLD As Worksheet
    Dim wshNEW As Worksheet
    Dim CELL As Range
    Dim i As Long
    Dim lROW As Long
    
    Set wshOLD = Thisworkbook.Sheets(1)	'sheet with 400,000 rows of annotations
    Set wshNEW = Thisworkbook.Sheets(2)	'sheet with 350 rows of genes
    lROW = wshNEW.UsedRange.Rows.Count - 1  '# of rows of data, excluding header
    
    'Loop through data on wshNEW
    For i = 0 To lROW
        strGENE = wshNEW.Range("A1").Offset(i, 0).Value
        'Use Excel's built in Find (Ctrl+F) to match device on wshNEW with wshOLD
        With wshOLD.Range("A:A")
            Set CELL = .Find(what:=strGENE, after:=.Cells(1048576, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not CELL Is Nothing Then     'if a gene was matched copy it's annotations to sheet 2
                CELL.entirerow.copy
		wshNEW.Range("A1").Offset(i, 0).pastespecial xlpastevalues
            End If
        End With
    Next i
    
    'Clear the clipboard
    Application.CutCopyMode = False
    
    'Turn on calculation, screenupdating, and alerts for normal Excel functionality
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    MsgBox prompt:="Success: Annotations copied to sheet two where matching genes were found"
    Exit Sub
    
    'Error Handling
Err1:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    MsgBox prompt:="Failure: The macro did not complete properly" & vbNewLine & Err.Number & " || " & Err.Description
End Sub

EDIT: Updated code for your situation

Old James fucked around with this message at 16:56 on Jul 17, 2012

# ? Jul 17, 2012 05:05

Sonic H: Dec 8, 2004; Me love you long time

Parks posted:

You can do this with a customer data validation.
Edit: No clue if this worked outside of Excel 2010

Highlight cell.
Data Validation > Custom
forumla is: IsNumber(cell) e.g. IsNumber(B2)

Copy the cell
Paste Special > Validation

Legend, cheers. Although whether it works in 2003 is another matter (don't ask).

# ? Jul 17, 2012 16:40

ScarletBrother: Nov 2, 2004

I have a really strange Excel 2007 problem.

I need to create a footer on one of my sheets in a workbook, but the field just isn't there under Page View. Header is available to create and I can insert default footers through the dropdown, but I can't edit those. This is a spreadsheet that contains formulas and formatting that my boss uses, so I can't just start over. I've tried google search to no avail.

This is bugging the poo poo out of me because I feel like I'm missing something obvious.

Please help, goons!

# ? Jul 18, 2012 16:08

stuxracer: May 4, 2006

ScarletBrother posted:

I have a really strange Excel 2007 problem.

I need to create a footer on one of my sheets in a workbook, but the field just isn't there under Page View. Header is available to create and I can insert default footers through the dropdown, but I can't edit those. This is a spreadsheet that contains formulas and formatting that my boss uses, so I can't just start over. I've tried google search to no avail.

This is bugging the poo poo out of me because I feel like I'm missing something obvious.

Please help, goons!

When you do Insert > Header/Footer and the view switches. Do you have "Go to Footer" in the ribbon?
You should be able to edit the footer then.

stuxracer fucked around with this message at 16:54 on Jul 18, 2012

# ? Jul 18, 2012 16:50

ScarletBrother: Nov 2, 2004

Parks posted:

When you do Insert > Header/Footer and the view switches. Do you have "Go to Footer" in the ribbon?
You should be able to edit the footer then.

"Go to Footer" and "Go to Header are both greyed out under the Insert > Header/Footer selection. I'm totally baffled.

# ? Jul 18, 2012 18:31

stuxracer: May 4, 2006

ScarletBrother posted:

"Go to Footer" and "Go to Header are both greyed out under the Insert > Header/Footer selection. I'm totally baffled.

Haha, that is awesome. I know protected mode does that, but if you can add things from the drop-down I doubt it is that. Check anyways.

Google has some other random things people posted that could be it.

# ? Jul 18, 2012 19:50

ScarletBrother: Nov 2, 2004

Parks posted:

Haha, that is awesome. I know protected mode does that, but if you can add things from the drop-down I doubt it is that. Check anyways.

Google has some other random things people posted that could be it.

Finally was able to find an answer! Or a workaround at least. It still makes no sense why the issue popped up in the first place.

1. Go to Page Layout tab. Under Gridlines and Headings, click the little arrow in the Sheet Options box. This will open the Page Setup dialong box. Click Header/Footer tab and then click Custom Footer. This will allow editing of both the header and footer.

2. Click the "Office Button" and point cursor at Print, then choose Print Preview. Click on the Page Setup icon to bring up the Page Setup dialog box and click on Header/Footer, etc.

# ? Jul 18, 2012 20:17

diremonk: Jun 17, 2008

My work loves to use Excel for things other than its intended use. Case in point, every day we create new spreadsheets for each of our stations. We use them to write down any issues we have during the day.

Currently, we open up three different files, rename one cell to today's date, then save it with the station call letters and today's date. So one station would be THISTV071912.

What I would like to do is have one workbook with all three spreadsheets in it as different sheets, have the date entered automatically at the top, and then have it save as three separate files in different folders. Like so,

code:

--CBS
  CBS071912
--FOX
  FOX071912
--THIS
  THIS071912

The save location is a network address that doesn't change and is \\computername\frontdesk\DR .

Auto changing the date is easy enough within the excel sheet, its the separating the workbook into three separate files and renaming them that is throwing me off. Is this even possible?

# ? Jul 19, 2012 15:32

ScarletBrother: Nov 2, 2004

diremonk posted:

My work loves to use Excel for things other than its intended use. Case in point, every day we create new spreadsheets for each of our stations. We use them to write down any issues we have during the day.

Currently, we open up three different files, rename one cell to today's date, then save it with the station call letters and today's date. So one station would be THISTV071912.

What I would like to do is have one workbook with all three spreadsheets in it as different sheets, have the date entered automatically at the top, and then have it save as three separate files in different folders. Like so,
code:
--CBS
  CBS071912
--FOX
  FOX071912
--THIS
  THIS071912
The save location is a network address that doesn't change and is \\computername\frontdesk\DR .

Auto changing the date is easy enough within the excel sheet, its the separating the workbook into three separate files and renaming them that is throwing me off. Is this even possible?

Try this as a Macro:

code:

Sub SaveEachWS() 
    Dim ws As Worksheet 
     
    For Each ws In ThisWorkbook.Worksheets 
        ws.Copy 
        ActiveWorkbook.SaveAs Filename:=ws.Name 
        ActiveWorkbook.Close 
    Next ws 
     
End Sub

# ? Jul 19, 2012 15:45

diremonk: Jun 17, 2008

Ok, that seems to work. But it is saving it to my document folder. I did a bit of searching and added this code to the macro but that doesn't seem to work along with having the wrong date type.

code:


Sub SaveEachWS()
    Dim FilePath As String
    Dim NewName As String
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Dim ws As Worksheet
     
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=ws.Name
        FilePath = "C:\Users\user\Desktop\test": NewName = FilePath & "file" & Format(Date, "MM-DD-YYYY") & ".xlsm"
    Next ws
     
End Sub

# ? Jul 19, 2012 16:46

ScarletBrother: Nov 2, 2004

code:


Sub SaveEachWS()
    Dim FilePath As String
    Dim NewName As String
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Dim ws As Worksheet
     
    For Each ws In ThisWorkbook.Worksheets
        ws.Copy
        ActiveWorkbook.SaveAs Filename:=ws.Name
        FilePath = "\\computername\frontdesk\DR": NewName = FilePath & "file" & Format(Date, "MMDDYY") & ".xlsm"
    Next ws
     
End Sub

Does this do the trick?

# ? Jul 19, 2012 16:50

diremonk: Jun 17, 2008

ScarletBrother posted:

Does this do the trick?

Not really, it is still saving the files to the My Documents folder and not appending the date onto the end of the file name. I checked with my IT admin and the share is not password protected, or at least I don't think so since I have permissions to write to it.

I tried to start over from scratch and it just generated about 15 new excel files on my desktop. I'm using Excel 2010 on Win 7 if that is any help. I really appreciate the assistance.

# ? Jul 19, 2012 17:28

ScarletBrother: Nov 2, 2004

Got it!

code:

Sub CreateWorkbooks()
    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim sht As Object
    Dim strSavePath As String
     
    On Error GoTo ErrorHandler
     
    Application.ScreenUpdating = False
     
    strSavePath = "C:\My Docs\" 'Your Pathname here
    Set wbSource = ActiveWorkbook
     
    For Each sht In wbSource.Sheets
        sht.Copy
        Set wbDest = ActiveWorkbook
        wbDest.SaveAs strSavePath & sht.Name & Format(Date, "MMDDYY") & ".xls"
        wbDest.Close
    Next
     
    Application.ScreenUpdating = True
     
    Exit Sub
     
ErrorHandler:
    MsgBox "An error has occurred. Error number=" & Err.Number & ". Error description=" & Err.Description & "."
End Sub

# ? Jul 19, 2012 18:45

Stealthgerbil: Dec 16, 2004

What is the best way to have values from one worksheet display in a cell in another worksheet. Not in a range or anything, just like cell C6's value will display in the other cell.

Also is there anything wrong with just being like

='[filenameblah.xlsm]WORKSHEETNAME'!C6

?

The value comes over and it works. Before I was using a vlookup that was like =VLOOKUP(location of the cell, location of the cell, 1) and it worked. However it seems like a lot for what I need to do.

# ? Jul 23, 2012 21:14

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Stealthgerbil posted:

What is the best way to have values from one worksheet display in a cell in another worksheet. Not in a range or anything, just like cell C6's value will display in the other cell.

Also is there anything wrong with just being like

='[filenameblah.xlsm]WORKSHEETNAME'!C6

?

The value comes over and it works. Before I was using a vlookup that was like =VLOOKUP(location of the cell, location of the cell, 1) and it worked. However it seems like a lot for what I need to do.

If you're in the same workbook you don't need the [filename] notation, just the worksheet name should do it. And yep, that's totally fine. If the data you want to display will always be in C6, there's no need to use anything other than a direct cell reference. Save VLookups for when you're actually looking within a table for some data.

# ? Jul 23, 2012 21:19

Stealthgerbil: Dec 16, 2004

Now that I got that working, I was wondering if it is possible to make the file location that it is retrieved by determined by a cell value. Lets say I have the worksheet determine the file name and it ends up being like 'filenameblah.xlsm'. How would I make it check like
='[C2]WORKSHEETNAME'!C6
where the name of the file is in cell C2?

# ? Jul 23, 2012 22:35

DukAmok: Sep 21, 2006; Using drugs will kill. So be for real.

Stealthgerbil posted:

Now that I got that working, I was wondering if it is possible to make the file location that it is retrieved by determined by a cell value. Lets say I have the worksheet determine the file name and it ends up being like 'filenameblah.xlsm'. How would I make it check like
='[C2]WORKSHEETNAME'!C6
where the name of the file is in cell C2?

=INDIRECT("["&A1&"]Sheet1!B1")

Will make the cell in your current workbook equal to the value of cell B1 on Sheet1 in the workbook named whatever is in the current workbook's A1. I've only done this sort of stuff as a temporary thing, where both workbooks are currently opened, but I believe this will still work if the referenced workbook is closed, as long as you provide the full path.

# ? Jul 23, 2012 22:52

diremonk: Jun 17, 2008

ScarletBrother posted:

Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off.

If I could ask a bit more though, is is possible to have the individual sheets save to different folders? Or maybe point me in the direction on how to do it myself? It's kind of fun to get a nice macro or worksheet in Excel and look like a smart person to the boss.

Again, thank you for working on this.

# ? Jul 24, 2012 06:12

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 16:12

ScarletBrother: Nov 2, 2004

diremonk posted:

Thanks this works great, although I haven't tested it yet on the network share portion. Sorry if took a couple days to get back to you about it, had to take a couple days off.

If I could ask a bit more though, is is possible to have the individual sheets save to different folders? Or maybe point me in the direction on how to do it myself? It's kind of fun to get a nice macro or worksheet in Excel and look like a smart person to the boss.

Again, thank you for working on this.

You should be able to write a separate save path for each sheet. I can't take full credit for it, I did a fair bit of googling and copy/pasting from other forums. For the bit you want, I'd just search for 'different save paths' or something like that. Should be able to fit it in.

Happy to help!

# ? Jul 24, 2012 15:30

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > Small Excel Question Not Worth Its Own Thread

«‹›82 »