Posts by bwkisley

Example - I have an exclusive file that I'll be giving to a group of people where I want to retain the master copy of this file and restrict user #1 from sharing it / sending to a new user without me approving it. Is there any way this can be done?? If this can't be done, I am afraid that once user #1 gets this file, there is no way of stopping that person from sharing it to 20 others.



Thank you in advance!

The Idea would be to insert the picture on a tab called "Welcome" - let's say in cell A15

Then on other sheets within the document it would autopopulate that image.

Let's say Sheet2, Sheet3, and Sheet4 in cell A1 would show whatever image was pasted/inserted into cell A15 on the Welcome page.

Otherwise the user will need to paste the picture multiple times.

It's not a huge hassle, just something that I feel could make this more user friendly

Hi Jerry - that is not an option I can select. When I copy my picture, and go to the ribbon and click the paste drop down, I only get "use Destination Theme" , "Keep source formatting", and "Picture". I then select the "Paste Special" and when I do that I see the "Paste Link" option but it will not allow me to click on it

Below is my current code. It works exactly as I need after a ton of different changes I have made. The issue is...the code takes like 30 seconds to run. It is not a dealbreaker however it is not very efficient. Any ideas on how I can speed this up??

Quote

Sub CreateInvoice()

Range("A11:u500").Select

Selection.Copy

Range("B12").Select

Selection.End(xlDown).Select

Sheets("Invoice").Select

Range("z9").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Rows("10:514").EntireRow.AutoFit

Dim c As Range

For Each c In Range("A10:A510").Cells

If c.Value = "True" Then

c.EntireRow.Hidden = True

End If

Next c

Range("AA1:AL1").Select

Selection.Copy

Sheets("Invoice History").Select

Range("B4").Select

Selection.End(xlDown).Select

Selection.End(xlDown).Select

ActiveCell.Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Invoice").Select

Range("B2:D6").Select

End Sub

Display More

Figured it out.... =IF(B6="","",INDIRECT("'"&B6&"'!M10")) is the answer in case someone else is looking for this

I have a list of names in cells B5:B14 on Sheet1

Each name in B5:B14 has it's own tab that is the same name listed in B5, B6 and so forth

Then on each tab for the names, in cell M10 I have a value

What I need to do is create a formula on Sheet1 that looks at the name in B5, goes to the tab, pulls cell M10, and puts that value in C5

Example

Cell B5 on Sheet1 = John Smith

Cell M10 on John Smith is 100

I need --> a formula in cell C5 on Sheet1 to show 100

I know this can be done with CONCATENATE somehow, I just can't seem to get it to work

It could be a lot. I thought it would be good to have a database of all invoices for history reasons. There wouldn't be any maintenance or anything to the tabs there would just kind of be there. Maybe it would be better to have a specific file that just holds invoices instead of adding to this file. Probably sounds like a better alternative now that I think about it.

Attached is an example file with a more detailed/updated description of what I am after. THANK YOU IN ADVANCE FOR ANY HELP!FileNeedsHelp.xlsm

I have a blank template the user will fill out. This template can have 1 completed row or 10+ rows of data. I would like to be able to click a button that will then populate a sales invoice automatically. I would need the sales invoice to only populate with the rows selected. Either (A) I need to have the macro require the user to select the row(s) desired to add to the sales invoice and then populate or (B) have a column that allows the user to check a box or something before clicking the macro button which will then look for the check boxes and only populate that rows data.

I am more than OK with you offering a better solution to this. I really do not know how to do this....I can do this no problem with one invoice per one client (each tab is named a client) however I could have different projects throughout the year for the same client and would only want to show an invoice for project 3 for example not 1 and 2.

I will be doing v lookups to pull the other data in the row, so all I am looking for is to find/select/checkmark/etc. something in column B, and paste in column AA

Is something like this even possible??

I have no clue if this is possible - and hoping to find out from you experts if it is.

My goal is to have a designated cell on my "Welcome" page that will be used as a dropbox of sorts for a logo.

I then have multiple sheets within my document where I would like that same logo to be without having to copy and paste it to each location

Is there anyway to have a cell linked to a cell where a picture is and show that same picture?

Example A1 is designation logo area

cell B1 is somehow linked to Cell A1.

When A1 doesn't have a logo in it, B1 is blank

When A1 has a logo in it, B1 automatically shows the logo.

Thank you Roy! This file is working exactly as I hoped! I very much appreciate all you have done!

Sorry, yes sorry I meant sort in alphabetical order those created sheets. It is not crucial, just a would be nice kind of thing. I'm extremely happy with your code as it is so thank you again!

You Sir are a GENIUS! Now I will just need to add in my create hyperlink for each client code and maybe look into finding out how to alphabetize these created tabs and it will be perfect! Thank you so much for your help!

That's exactly what the code in the other thread does. It looks at a range of data, finds the unique values that are not currently a sheet name and then creates a new sheet with that unique value as a name. If the value is already a sheet name, it skips it. Each new sheet is a copy of a "master" blank template sheet aka my "Blank Client" sheet.

My concern with this though is each client could have multiple projects. So having each client have their own sheet where the user can populate each project detail I think would be best still. Otherwise with using Vlookups and Index/Match you might not capture every single project description. I already have a macro in my full version that auto-populates a hyperlink to the correct sheet which will go in the "Jump to" column on the project tracker tab. And on Each tab a hyperlink to take them back to the Project Tracker. So it will be very user friendly and easy to switch back and forth.

I unfortunately can not visualize what you are recommending. I am very comfortable with Vlookups - ok with index/match - but not sure how that would allow me to be able to see the Project details for each client. Are you saying like a drop down with all client names that the user picks then a vlookup to pull that data relative to that client? "Populate the sheet from a database of projects" is where I am getting a bit hung up

The user would fill out the "Project Tracker" data table. As part of a macro I will then refresh the pivot table on the "Data Validation" tab which will pull all unique names. This was my way around avoiding duplicate sheets for the same person since I was struggling figuring that out. So the list would start from cell A2 on the Data Validation tab and go down from there for each new unique name

Attached is a dumbed down version of my file. Command button will go on the Project Tracker tabExample File.xlsm

By me saying "moderator closed the thread on me so I need to create my own" I didn't mean that as being upset, sorry if you read it that way. I did exactly what another person did in that thread and I had no problem setting a new thread on my own. I needed to provide some back story so someone wouldn't just say "this has been resolved already see here (the thread I posted).

Also you didn't attach anything if you were trying to help

Hello everyone! After days of trying to figure this out on my own and searching for help online, I finally found what I needed, but the moderator closed the thread on me so I need to create my own. The below link will take you to this thread.

Copy sheet and rename from a list, ignore duplicates

I need a macro that copies the "Blank Client" tab and creates a new tab for each unique value starting in cell A2 and going down on the "Data Validation" tab. Each newly created tab should be renamed to whatever is in cell A2, cell A3, cell A4......etc on this "Data Validation" tab. If a tab already exists with a value in column A, this Macro needs to skip/ignore it and do nothing. All new sheets should be added after my "Project Tracker" tab (ideally) otherwise at the end. Ideally, this would alphabetize the tabs as well starting after my "Client Tracker" tab so each name in cells A2:A??? on the "Data Validation" tab would be in ABC order.

Hopefully this all makes sense and thank you so much in advance for any support you can provide!!