Hi everyone,
I currently have a worksheet which populates with data depending on an account number entered on a specific cell. What I'm trying to do is develop a macro which will populate the cell from each item in a list (the list is located on between A1 - A700). Each time the cell is populate with an item going down the list, I'd like the worksheet with the new results saved using the account number and current Year-Month saved in a specific location. Any ideas?
Thanks very much guys!!
You can save the file with this macro command
where StrPath contains the drive path name and ends with \.
Please Login or Register to view this content.
It would be easier to figure out the rest if you post your sheet.
If this helped, please click (*) Add Reputation
Hi Hectop,
Thanks for responding. Attached is a sample sheet. So, the way the sheet works is as follows:
- There is a list of items on the Data sheet from A1 to A10
- On the Summary page, when one of the items on the Data sheet is entered into cell B1, cells A10 to H16 are automatically populated with data from other data sheets which have not been attached to the sample sheet you have.
- What I'd like to do, is to develop a macro which will populate each item in the Data sheet cells A1 to A10 into the Summary sheet cell B1.
- When each item is put into cell B1, cells A10 to H16 populate with their corresponding data. At this time, I'd like the macro to save the Summary sheet using the contents of the cell B1 (which is from the data sheet A1-A10), and beside it say Core, and Summary. If there's a way I can identify a single month in the saved file name for all the items in the Data sheet, that would be great too.
Thanks very much for you assistance.
bump. Any help?
Sorry, but can't work on it at work.
Hectop
Absolutely. Just wanted to see if anyone else had any thoughts. I look forward to your help with my thanks.
This code should do what you want.
Please Login or Register to view this content.
hectop,
Thanks very much for your reply! I will try the code out and let you know how it goes. You're help is very much appreciated!!
If I can ask; you indicated Cells(1, 2) a number of times. Does that means row 1 column 2?
Also, is there a way the code can be slightly altered to allow me to pick the folder the reports should be saved to in File Explorer?
Thanks!!
Last edited by Groovicles; 11-23-2013 at 05:01 PM.
hi hectop,
You are a genius. The code works perfectly!! Thanks so much. A question though, how could I change the code so that both the Summary and Data sheets are saved on the new workbooks? Also, is there any way I can get file explorer to pop up so I can select where all the reports should be saved myself?
Thanks very much!
To copy the Data sheet, copy/modify the lines below the comment "'Copy the Summary sheet to the new workbook"
There is a way to pop up file explorer. I'll have to find the code and post it tonight. You can try searching in this site. It has been addressed before, just don't remember which question off hand.
This should do everything you want. If you want to change the file extension to save as, you also need to change the FileFilter value
Please Login or Register to view this content.
Hectop,
You're fantastic bud. Thanks very much for all your help.
No problem. Just mark is solved and click on Add Reputation.
Hey Hectop
The forum won't let me add any more reputations.
I ran into a bit of an problem. The macro copies the summary and data sheet just fine. However, the copied data sheets retain the original formulas from the template tool where it retrieved all the data which is summarized inthe summary sheet. Is there any way I can keep the values of the cells in the data sheet without having them link back to the original template?
Thanks!!
I'll check what would be the best way
Thanks very much. I'm using the original macro you had created so i hope we can add the paste value stuff in that one. Again, I'm try to have the values listed in Summary and Data retained on the new workbook and not point back to the original template.
This is it with some minor changes (mostly just to add the second worksheet:
Option Explicit
Dim iRow As Integer
Dim sFName, sPath, sWBCur, sWBNew As String
Dim wsNew As Worksheet
Sub InterateSht2()
sWBCur = ActiveWorkbook.Name
sPath = "C:\stuff" 'Specify the drive\path you want to save to
For iRow = 1 To 10
Sheets("Summary").Cells(1, 2).Value = Sheets("Data").Cells(iRow, 1).Value
'populate A10:H16 here
'Build the save file name
sFName = Format(Now(), "yyyy mmm ") & Sheets("Summary").Cells(1, 2).Value & " Summary" & ".XLSX"
'Create a new workbook that will contain the new summary sheet
Workbooks.Add
sWBNew = ActiveWorkbook.Name
'Copy the Summary sheet to the new workbook
Windows(sWBCur).Activate
Sheets(Array("Summary", "Data")).Copy Before:=Workbooks(sWBNew).Sheets(2)
'Save the new workbook & close
Windows(sWBNew).Activate
ActiveWorkbook.SaveAs Filename:=sPath & sFName
Windows(sFName).Close
Next iRow
End Sub
Last edited by Groovicles; 11-26-2013 at 03:30 PM.
Hi everyone, sorry I had to bump this.
Hectop has been incredibly giving in helping me build the VBA code listed above. And while I would normally wait for his response, this code in a modified for is being used for a work project I'm on.
I have a template built which populates certain cells based on an identifer number entered in a specific cell. Hectop created the VBA code listed above which runs a list of these identifier numbers through my template, and then copies the summary and data pages onto a new workbook and saves them based on a specific naming convention; bloody brilliant.
What I'm trying to do how is adjust Hectop's code to have the values generated on the summaries pasted on as values rather than with formulas linking them back to the original template. I understand the PasteSpecial code would be used, but I'm not sure how to incorporate it. Like most of us, Hectop is a busy guy. I would normally wait for the response from anyone who would be helping me, but this help is related to a work project I'm on.
Thanks very much for your help, everyone!!
Can you post a sheet with the references/formulae?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks