Excel VBscript

I have a VBScript that copies data from my daily sheet to a monthly sheet. It is working great but I need it to check for the next line that is available and pate the data to that line. Is this possible. I will upload the code  I have. Thank you for any help with this.
jodyreidIT ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jodyreidIT ManagerAuthor Commented:
sorry never upload file. Hope someone can help. I need the check between these 2 lines

 MsgBox "Patient number is " & PatientNumber ' just checking. Delete this row once you are confident things run fine

    Workbooks.Open Filename:=InputPath & InputFile

Thank you
0
Glenn RayExcel VBA DeveloperCommented:
Sorry, but you will have to be more specific as to your request and/or provide the existing code you want assistance with.
0
jodyreidIT ManagerAuthor Commented:
We have a spreadsheet with daily data 1 for each person. At the end of the month I need to copy all the data for each person and paste it to 1 spreadsheet The 2 spreadsheet are "dailyData" and "MonthEnd" I would like to setup a VBscript to copy all data to the "MonthEnd" sheet but the script would need to check for the next available line to past the each persons data. This would be a Script that would run on the "DailyData" Spreadsheet. I am going to upload the Script that I have now. This works great but outputs to one file and with the persons chart number. I need to have them output to one chart but to the next available line.
Thank you.
Copy-data-to-another-sheet.txt
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

jodyreidIT ManagerAuthor Commented:
I have changed my code for my script. It is also working but it should look for the next line that is empty and paste the rest of the data but it just paste over itself. I also need to know how to get it to copy from "A4" to "A17" and so on in one go. Any help would be great. Thank you
VBscript.txt
0
Glenn RayExcel VBA DeveloperCommented:
I believe this code will paste your data on the next available row in the new workbook:
 
Private Sub CommandButton1_Click()
    Dim PatID, CharCd As String
    Dim Qty, UnitPr As Double
    Dim SerDate As Date
    Dim ThMd1, ThMd2 As Variant
    Dim myData As Workbook
    Dim lngRowCount As Long
    
    Worksheets("Sheet1").Select
    PatID = Range("A4").Value
    CharCd = Range("B4").Value
    Qty = Range("C4").Value
    UnitPr = Range("D4").Value
    SerDate = Range("E4").Value
    ThMd1 = Range("F4").Value
    ThMd2 = Range("G4").Value
    
    Set myData = Workbooks.Open("C:\Users\administrator\Desktop\Data\PCCExport.xlsx")
    Worksheets("Sheet1").Select
    lngRowCount = Cells.SpecialCells(xlLastCell).Row + 1
    Cells(lngRowCount, 0) = PatID
    Cells(lngRowCount, 1) = CharCd
    Cells(lngRowCount, 2) = Qty
    Cells(lngRowCount, 3) = UnitPr
    Cells(lngRowCount, 4) = SerDate
    Cells(lngRowCount, 5) = ThMd1
    Cells(lngRowCount, 6) = ThMd2
   
    myData.Save
    myData.Close
End Sub

Open in new window


I don't know what kind of values ThMd1 & ThMd2 are, so I declared them as type Variant, but you can certainly change that.

I don't understand your follow-up request regarding copy[ing] from "A4" to "A17"; could you be more-specific?


Regards,
-Glenn
0
jodyreidIT ManagerAuthor Commented:
There are 13 rows of data but this is only coping over the first row. I need to copy over all 13 rows and have them paste one after another in the PCCExport file. Thank you.
0
jodyreidIT ManagerAuthor Commented:
Thank you for the help but the code stops at   "Cells(lngRowCount, 0) = PatID" and does not paste any data. Any help with this. Thank you.
0
Glenn RayExcel VBA DeveloperCommented:
I am re-stating your issue in order to make sure I understand what you need.

You have a workbook called "Daily Data" that contains several rows of information on patients (your example mentions 13 rows).   This information is in "Sheet1".  You want to copy this information to another workbook (see below) on "Sheet1" beginning with the first available row past any existing data (i.e., first blank row).

You mentioned a workbook titled "MonthEnd.xlsx" (in your original code) and now a new one called "PCCExport.xlsx" (in the latest code).  Which one is the destination workbook?

-Glenn
0
jodyreidIT ManagerAuthor Commented:
Yes all is correct and the spreadsheet is MonthEnd.xlsx. The other I was only using to do some testing. Sorry about that.
Thank you for your help with this.
0
Glenn RayExcel VBA DeveloperCommented:
This subroutine will copy all the contiguous cells in your source sheet (i.e., no blank rows may exist in the patient data), open the MonthEnd.xlsx workbook, and paste this data in the first blank row of column A (starting from cell A1) on "Sheet1"
Private Sub Copy_Patient_Data()
    Dim myData As Workbook
    
    Worksheets("Sheet1").Select
    Range("A4:G" & Range("A4").End(xlDown).Row).Copy
    
    Set myData = Workbooks.Open("C:\Users\administrator\Desktop\Data\MonthEnd.xlsx")
    Worksheets("Sheet1").Select
    Range("A1").End(xlDown).Offset(1, 0).Select
    Selection.PasteSpecial
    Application.CutCopyMode = False
  
    myData.Save
    myData.Close
End Sub

Open in new window


-Glenn
0
jodyreidIT ManagerAuthor Commented:
Thank you but it only runs to this line and stops."Range("A1").End(xlDown).Offset(1, 0).Select". Am I doing some thing wrong.
Thank you
0
Glenn RayExcel VBA DeveloperCommented:
If it stops it must be presenting an error message; what is the error?

If Sheet1 in the MonthEnd.xlsx sheet is blank - or if there is only a header row - an error would occur.  The code presumes that data already exists in that sheet.
0
jodyreidIT ManagerAuthor Commented:
The error is "Run-time error '1004' Select method of Range class failed. I have data in all cells. Any help with this. Thank you
0
Glenn RayExcel VBA DeveloperCommented:
You'll have to post either an example of Sheet1 in your MonthEnd.xlsx file or a screenshot.  This error will not happen if there is at least two cells with data in column A (A1 and A2).

The error WILL happen if there is either
1) no data in column A in Sheet1, or
2) only a value in cell A1 in Sheet1

This error happens because Excel is trying to use logic that moves the active cell to the end of a contiguous set of data (the .End(xldown) part).  This is like pressing [End] and the down arrow on your keyboard.
0
Glenn RayExcel VBA DeveloperCommented:
An alternate method of selecting that next blank row (although not best practice, IMO) would be to replace line 9 of the code with this:
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

Open in new window


This starts at the last row in column, then moves up to the first, non-blank cell and then back down one row.

-Glenn
0
jodyreidIT ManagerAuthor Commented:
I am uploading a screenshot of my MonthEnd.xlsx sheet 1. I tested you new line 9 code and it did not work. At the start of each month there will be no data in the MonthEnd file. Any help will be great. I am not strong on VBscripts. Thank you.
0
jodyreidIT ManagerAuthor Commented:
I forgot to press the upload button. Here is the screenshot. Thank you again
Untitled.jpg
0
Glenn RayExcel VBA DeveloperCommented:
I just tested this subroutine with mockup version of your MonthEnd.xlsx file set up exactly as in your example and it worked.
Private Sub Copy_Patient_Data()
    Dim myData As Workbook
    
    Worksheets("Sheet1").Select
    Range("A4:G" & Range("A4").End(xlDown).Row).Copy
    
    Set myData = Workbooks.Open("C:\Users\administrator\Desktop\Data\MonthEnd.xlsx")
    
    Worksheets("Sheet1").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial
    Application.CutCopyMode = False
  
    myData.Save
    myData.Close
End Sub

Open in new window


Example files attached.  The EE-DailyData.xlsm file has the macro to run.

Regards,
-Glenn
EE-DailyData.xlsm
MonthEnd.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jodyreidIT ManagerAuthor Commented:
Thank you for all your help on this. It is working great now. Thanks again
0
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.