Posts by SaraO

Re: Incrementing a value from another workbook

I am trying to use this code for a number set that does not contain the "-" and I get a run time error highlighting this line in the code...

l = w.Sheets(sheet).Range(ref).Value + 1

Any idea on how to fix that?

Re: Extract rows of data meeting criteria

Thank you Trebor76!

It works like a charm. I appreciate you taking the time to help me out with this.

Hello,

I am trying to extract data from a closed workbook meeting specific criteria.

This is what I have...

A Workbook called "InvoiceList.xlsx", that stores a list of invoice that have been created by multiple sales staff. This workbook needs to stay closed so when a new invoice is created it can store the info in there.

Each row contains information as following...

A = Invoice Number in the this format Inv-170152
B = Date invoice was created
C = Sales Staff that created the invoice
D = Customer's name
E = Before tax amount
F = With tax amount
G = Job number
H = Quote number

What I need...

Each sales staff needs to have his/her own list of invoices they created. I have created new workbooks for each sales staff where I would like to extract their invoice list into.

I need a code that would look for "John Bergen" in column C and extract all the data from that row A - H and paste it in "InvoicesJohn.xlsm" in the next empty row. A1:H1 are column headers so the pasting needs to start at A2.

Re: VBA to tranfer data and rank teams in Softball

That's right

Re: Keep template margins setting when saving

Good morning KjBox,

It's giving me an error on the following line...

PapSize = .XlPaperSize

Re: Keep template margins setting when saving

Good morning,

I finally got a chance to test the code and it is still not saving it with the original margins. It still shows up on two pages when I open the saved document.

Hello,

I have been asked to create a spreadsheet for a fundraiser softball tournament. I have most of it figured out but I'm having trouble with transferring numbers and team ranking.

The way this spreadsheet works...

Info Sheet: Here is where the team names, game dates and times are entered to feed the rest of the sheets.

Game Schedule: I have created dropdown lists to select the teams, dates and times for each regular games being played.

Game Results: Here is where the teams runs get entered and it automatically calculates the differentials.

Team Wins: The wins and differentials for each game transfer to this page and adds up the wins and differentials.

** On this page I have not yet figured out how to transfer the wins and differentials from the Game Results page**

Team Rankings: This is where I want to see what position each team is in after each games and what teams will make it to the semifinals and finals.

**This is where I'm having trouble with the team ranking**

I have been struggling with this for months already and can't seem to get it all working correctly. My deadline has come and long gone

I know very little about softball tournaments so if anyone sees room for improving this, please let me know.

Thank you!

Re: Keep template margins setting when saving

It still gives me the same error on the same line.

Re: Keep template margins setting when saving

I'm getting and error message "Object doesn't support this property or method" and highlights this row...

dMTop = .TopMargin

Re: Keep template margins setting when saving

Thank you very much! I will give it a try.

Hello,

I have a code that saves a copy of a template in several files. Its work really well but it doesn't keep the margin setting on the saved document. When we have to go in and reprint that document we always have to reset the margins or it will print on multiple pages.

How can this code be modified to save margins as they are set in the template?

Public Sub Save_Receipt()


   Application.ScreenUpdating = False
   GetReceiptNumberRun
   SendReceiptToMaster
   Dim SaveAsFileName   As Variant
   Dim newWorkbook      As Workbook


   SaveAsFileName = "C:\Users\Administrator\Desktop\Shared\Receipts\" & Range("I10").Value & ".xlsx"
  
   Set newWorkbook = Workbooks.Add(XlWBATemplate.xlWBATWorksheet)


   ThisWorkbook.Worksheets("Receipt").Copy Before:=newWorkbook.Worksheets(1)


   'Tidy up copied Invoice sheet
   With newWorkbook.Worksheets(1)
      'Convert formulas to their values
      .Range("I11").Value = .Range("I11").Value       'Date
      .Range("J16:J48").Value = .Range("J16:J48").Value   'Totals
      .Range("H41:H46").Value = .Range("H41:H46").Value   'Hiden formulas
      .Range("B1").Value = .Range("B1").Value         'Hiden Formula
      'Delete data validation dropdowns
      .Range("B10").Validation.Delete
      .Range("I12").Validation.Delete
      .Range("F42").Validation.Delete
      'Delete unwanted columns, including hidden lookup table
      .Columns("K:AZ").Delete
   End With


   'Suppress all Excel warnings: deleting sheet; saving as macro-free workbook; file already exists
   Application.DisplayAlerts = False


   'Delete empty sheet
   newWorkbook.Sheets(2).Delete


   'Save new workbook in xlsx format
   newWorkbook.SaveAs SaveAsFileName, xlOpenXMLWorkbook
   newWorkbook.Close False


   PrintReceipt


   'Clear Receipt
   Clear_Receipt


   'Reenable Excel warnings
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True


End Sub

Re: Modify code to transfer data to template

I got it figured out!!

For some odd reason this is working...

addme.Offset(0, 1) = Me.lstConstructionRetail.List(X, 1)
         addme.Offset(0, 2) = Me.lstConstructionRetail.List(X, 2)
         addme.Offset(0, 7) = Me.lstConstructionRetail.List(X, 3)

Thank you so much for your help! I really appreciate the time you took for me.

Re: Modify code to transfer data to template

It's a pretty large workbook. Can I email it to you?

My list has the Rev Code, Item Description, Supplier and Price.

Re: Modify code to transfer data to template

That is giving me a runtime error "Could not get list property. Invalid argument". I changed my list to 5 columns to see if that would work and the error doesn't come up but still doesn't put the price in column I.

Re: Modify code to transfer data to template

Oh ooppss that second 2 should not be there. I have been playing with the code for a couple days now and must have changed that at some point.

I did go back into my template and corrected that and it still does not put the last item in the list box in column I.

I'm trying to write to columns B, C (C:H Merged) and I. The I column is where I'm having trouble.

(Column A is where the quantity is typed in)
Column B = Rev Code (First item in the list box)
Column C (C:H Merged) = Item Description (Second item in the list box)
Column I = Item Price (Fourth item in the list box)

The third item in the list box is the supplier and is there for sales staff info only and does not need to appear on the Invoice.

Hope that makes it a bit clearer.

Thank you!

Hello,

I have a code that I have been using for some time now. I have to make some changes which requires adding another column to the template but I am having trouble getting the data to transfer to the correct cells.

The code I have is as following:

Private Sub cmdAddConstructionRetail_Click()
   Dim addme            As Range
   Dim X                As Integer
   Set addme = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
   For X = 0 To Me.lstConstructionRetail.ListCount - 1
      If Me.lstConstructionRetail.Selected(X) Then
         addme = Me.lstConstructionRetail.List(X)
         addme.Offset(0, 1) = Me.lstConstructionRetail.List(X, 1)
         addme.Offset(0, 2) = Me.lstConstructionRetail.List(X, 2)
         addme.Offset(0, 2) = Me.lstConstructionRetail.List(X, 3)
       Set addme = addme.Offset(1, 0)
      End If
   Next X
   For X = 0 To Me.lstConstructionRetail.ListCount - 1
      If Me.lstConstructionRetail.Selected(X) Then Me.lstConstructionRetail.Selected(X) = False
   Next X
End Sub

I have populated the list box with a dynamic name range list which is 4 columns wide but only 3 of those are needed on this particular template. One column is for information purpose only and is not needed on the template.

If I use this code on a test sheet without merged cells it works just like I need it to. But my template has merged cells where the second column of the name range is going. It places the first item from the list in the second column on the template, second item in the third (Merged) column and fails to place the last item on the list in the fourth column.

I have attached a screenshot of the template, Hope that helps.

Thank you in advance for your help.

Re: User Form adding, searching, editing and deleting data in a different book

Thank you for your help.

I have followed all your instructions and parts seem to work. I am able to search for customers!

If I add a new customer it does not show up in the SourceData.

I can not edit customers either.

Is there anything else that might need to be changed?

Re: User Form adding, searching, editing and deleting data in a different book

I want to store the address in 2 lines.

Now they are stored as:

B - C - D

First & Last Name - Box 1415 Morden MB R6M 1B3 - 204-822-8745

This is how I need them to be stored:

B - C - D - E

First & Last Name - Box 1415 - Morden MB R6M 1B3 - 204-822-8745

I need to add another text box in the userform to separate the address as shown above.

Hope this makes sense

Re: User Form adding, searching, editing and deleting data in a different book

Hello Grimes0332,

I was wondering if you could help me with changing the address to 2 lines. The way the invoice prints the address is in the right place for window envelopes but we need the address in 2 lines.

I would really appreciate your help once again.

Thank you!

Re: User Form adding, searching, editing and deleting data in a different book

Thank you so much!!! You are my hero!! I will give it a test run on the network with multiple users and see how it holds up.

When I first started with this, the idea was to have the work orders automated the best I could. But as time and progress went on the bosses kept asking to add this n that and this is where I am at now. After a couple years of searching for the "Perfect" software to run the company they just gave up. I have been learning everything as I go and as I'm learning more I realize there could be better ways to do this. But at this point I have spent a crazy amount of hours on this the bosses would not be impressed if I started all over.

I really appreciate all the help I have gotten in this forum, without that help I would not have been able to accomplish this. Thanks to all those that have helped me I got a raise!!!