Posts by my14string1
-
-
-
-
I am using a formula in column A to count the number of pump entries in column B starting at row 3. The number of entries in column B is unknown in length depending on the number of pumps in the hospital. I stopped the formula at row 11 (it might extend to row 100 for that day) because I need to modify the formula to take in account column C. If column C has a date (started scanning in new pump serial numbers that day) in it the cell counter in row A needs to start at 1 again and continue on until the next entered date in Column C. I would like to keep the function of column A showing no entry when column B has no entry although there should not be any blank cells in column B if every pump gets scanned into it. Notice the cell count in column A starts at 1 again because of date change. The last thing to think about would be a formula in the last cell row that would give a running total number of pumps in Column B. Any ideas would be greatly appreciated.
-
Re: Copy from one sheet to another sheet then save in another workbook
Thanks pike, now I see what you are talking about. I don't think it will be a problem because column "A" input is from a scanner and the individual will get feedback if there is no input. One last question, can an input box be used to change the Const csPath As String = "C:\...." just in case this program is run on a differnt computer which would need to change the path.
-
-
Re: Copy from one sheet to another sheet then save in another workbook
Yes that works great Pike why couldn't I see that? One question, where would you put an on error statement? If you leave out an entry in column A the program can't save the file because the file name comes from that blank cell (I changed the one line of code to save with cell data from column "A"). I don't think it should be a problem because that data is scanned in but I just wanted to cover that scenario (the error message is kinda cryptic.)
Thanks again! -
New to the forum, have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
Code
Display MoreSub MoveData() sName = "temp" Const csPath As String = "C:\Documents and Settings\mcgaulc\Desktop\TestFile\" 'MyName = ActiveWorkbook.Name Dim wsSource As Excel.Worksheet 'Allows for dynamic file naming as data changes in col A temp sheet Set wsSource = ThisWorkbook.Worksheets("Sheet1") Sheets("Sheet1").Select 'find the last row with data in the spreadsheet FinalRow = Range("A999").End(xlUp).Row For i = 1 To FinalRow Sheets("Sheet1").Select 'Copy the SN from temp sheet in a loop thru col A Range("A" & i).Copy 'Select the temp sheet to paste data into Sheets("temp").Select Range("E9").Select ActiveSheet.Paste ThisWorkbook.Sheets("temp").Copy 'Save the new document(Copy of) with a Sequential file name '& ".xls" allows to save copy of workbook as .xls file extention ActiveWorkbook.SaveCopyAs FileName:=csPath & wsSource.Cells(i, 1).Value & ".xls" 'Close this new word document ActiveWorkbook.Close False Next i Sheets("temp").Select 'Range("B1").Select 'Seletion.ClearContents End Sub
Here are some images of the data sheet and the saved sheet
[ATTACH=CONFIG]59952[/ATTACH] [ATTACH=CONFIG]59953[/ATTACH] Note: sheet2 in image is "temp" sheet in code
Thanks in advance for any response or ideas.