I'm using the following code to take calculated data in VBA and send it to specific cells on an excel sheet. The code I have used causes the data to show up on the first worksheet in the excel book in the specified cells. Does anyone know if it is possibly to put the data into a specific workbook and how I would modify the code?
Cells(3, 4) = DiameterFt
Cells(4, 4) = FloorThickness
Cells(5, 4) = FootingThi
You will need to modify as applicable to your situation.
Sub SendToClosed() Dim xNewApp As New Excel.Application Dim xNewWB As New Excel.Workbook Dim strFile As String 'You must change the file path and extension below as applicable strFile = "c:\forums\closedtest.xlsx" Set xNewWB = xNewApp.Workbooks.Open(strFile) xNewApp.Worksheets.Add xNewApp.ActiveSheet.Cells(3, 4) = "DiameterFt" xNewApp.ActiveSheet.Cells(4, 4) = "FloorThickness" xNewApp.ActiveSheet.Cells(5, 4) = "FootingThi" xNewWB.Save xNewWB.Close Set xNewApp = Nothing Set xNewWB = Nothing End Sub
Perhaps it was the Noid who should have avoided me...
If you are satisfied with my solution click the small star icon on the left. Thanks
1. Make a copy of your workbook and run the following code on your copy (just in case)
2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
3. Paste the code from above into the empty white space. Close the VBE.
4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".
Thats a little more complicated than I thought it would be so perhaps I asked my question in an incorrect way. Let me try again.
I have four worksheets (Sheet 1, Sheet 2, Sheet 3 and Sheet 4) in my open workbook. When I perform my calculation in VBA I can easily get the data to show up on the first worksheet (Sheet 1) using the "Cells (x,y) = Variable" format. What I can't seem to do is get data into the other sheets in the open workbook. So, for example, I can get the "DiameterFT" data to show up in Cells (3,4) on Sheet 1; however, I can't get it to show up in cells (3,4) on Sheet 3. I was thinking it might be as easy as using the name of the sheet prior to the "Cells" command.
Ok, so welcome to the closely spelled, but vastly different world of workbooks vs. worksheets.Originally Posted by Harvey72
You specified a workbook which is why I responded in the way that I did.
If you would like for it to appear on an additional sheet:
If you decide that it needs to appear on multiple worksheets, it might be time to consider the use of a For Each Next loop.With Sheets("Sheet1") Cells(3, 4) = DiameterFt ' Could also be expressed as Range("D3").Value = DiameterFt Cells(4, 4) = FloorThickness Cells(5, 4) = FootingThi End With With Sheets("Sheet3") Cells(3, 4) = DiameterFt ' Could also be expressed as Range("D3").Value = DiameterFt Cells(4, 4) = FloorThickness Cells(5, 4) = FootingThi End With
Thank you for your help and sorry that I misspoke on the worksheet versus workbook.
So, I tried it as follows but it didn't seem to work:
With Sheets("Labor")
Cells(41, 5) = BearingPads
End With
My worksheet is titled "Labor" of course and the data on the number of Bearing Pads should go into the cell described as row 41 and column 5. When I run the program, I get nothing in that cell. I have the workbook open but the worksheet is not active. Does the worksheet need to be the active worksheet?
If BearingPads is a single-cell named range, then you must specify it as such by:
With Sheets("Labor") Cells(41,5) = Range("BearingPads") End With
BearingPads is a value that is calculated in the VB code itself. Perhaps it might help if I explain what I'm doing.
I'm entering values in a UserForm. Based on those input values, I'm calculating values in VB that are then inserted back into the excell workbook at a click event called "Recalculate". I would like to insert the calculated values into specific worksheets instead of all the values going straight onto the active worksheet just behind the user form.
So, in the code I displayed, I'm calculating a value in VB which is BearingPads and then attempting to insert it in a specific cell on a specific worksheet within the workbook. That worksheet is titled "Labor".
I hope I'm making sense.
So BearingPads is a variable. What is it declared as? There is not a "as value" declaration. Generally speaking, the click event would include:
If the user does not need to see all of this happening, I would encourage the use of:ActiveWorkbook.Sheets("Labor").Activate Range("D4").Value = BearingPads.Value Range("D5").Value = <next variable>.Value Range("D6").Value = <next variable>.Value ActiveWorkbook.Sheets("<Next Worksheet Name You Want to Insert Into>".Activate Range("D4").Value = BearingPads.Value Range("D5").Value = <next variable>.Value Range("D6").Value = <next variable>.Value
(At the top of your code)
(And as you close out/wrap up)Application.ScreenUpdating = False
Hope this helps, -as-Application.ScreenUpdating = True
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks