Solved

Excel VBscript

Posted on 2014-09-15
20
118 Views
Last Modified: 2014-09-17
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.
0
Comment
Question by:jodyreid
  • 11
  • 9
20 Comments
 

Author Comment

by:jodyreid
ID: 40323752
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40323789
Sorry, but you will have to be more specific as to your request and/or provide the existing code you want assistance with.
0
 

Author Comment

by:jodyreid
ID: 40325464
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
 

Author Comment

by:jodyreid
ID: 40325923
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40325985
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
 

Author Comment

by:jodyreid
ID: 40325997
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
 

Author Comment

by:jodyreid
ID: 40326014
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326034
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
 

Author Comment

by:jodyreid
ID: 40326073
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326183
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:jodyreid
ID: 40326206
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326212
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
 

Author Comment

by:jodyreid
ID: 40326249
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326337
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40326371
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
 

Author Comment

by:jodyreid
ID: 40328069
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
 

Author Comment

by:jodyreid
ID: 40328076
I forgot to press the upload button. Here is the screenshot. Thank you again
Untitled.jpg
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40328301
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
 

Author Closing Comment

by:jodyreid
ID: 40328522
Thank you for all your help on this. It is working great now. Thanks again
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40328530
You're welcome.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now