will definitely try it out!!!
Posts by Rurkz
-
-
I currently have my invoice setup to simplify how i save my file as, which specific folder, then the option to send it as an email.
the minor issue i have is the pdf code. its saves to the correct business name folder but it also saves to the general invoice folder
for example
business name folder
\Invoice\business\Invoice 379 business.pdf
General Invoice Folder
\Invoice\379 business.pdf
how do i prevent it from saving to the general invoice folder and keep the same functions
Code
Display MoreSub EmailAspdf() Dim EApp As Object Set EApp = CreateObject("Outlook.application") Dim Eitem As Object path = "C:\Google Drive\Business Office Work\Accounting\Invoices\" invno = Range("F5") invna = Range("B3") address = Range("B22") custname = Range("B13") fname = invna & " " & invno & " - " & custname ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ IgnorePrintAreas:=False, _ filename:=path & fname Set Eitem = EApp.CreateItem(0) With Eitem .To = Range("B16") .Subject = " " & address & " - " & invna & " " & invno .Body = "Please Find Invoice Attached. If Paying By E-Transfer, Please Add Invoice Number or Address In the comment section of the E-Transfer!" .Attachments.Add (path & fname & ".pdf") .Display End With End Sub Sub SaveAspdf() Dim invno As Long Dim invna As String Dim address As String Dim custname As String Dim path As String Dim fname As String Dim wb2 As Workbook Dim ws2 As Worksheet Set wb2 = ThisWorkbook Set ws2 = wb2.Worksheets("Business Contacts") path = ws2.Range("F2") invno = Range("F5") invna = Range("B3") address = Range("B22") custname = Range("B13") fname = path & "\" & invna & " " & invno & " - " & custname & ".pdf" ActiveSheet.ExportAsFixedFormat Type:=x1TypePDF, IgnorePrintAreas:=False, filename:=fname End Sub Sub saveinvasExcel() Dim invno As Long Dim invna As String Dim address As String Dim custname As String Dim path As String Dim fname As String Dim wb2 As Workbook Dim ws2 As Worksheet Set wb2 = ThisWorkbook Set ws2 = wb2.Worksheets("Business Contacts") path = ws2.Range("F2") invno = Range("F5") invna = Range("B3") address = Range("B22") custname = Range("B13") fname = path & "\" & invna & " " & invno & " - " & custname 'copy the invoice sheet to a new workbook Sheet1.Copy 'then delete all the buttons on the worksheet Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type <> msoPicture Then shp.Delete 'This line is modified so that is doesn't delete the logo Next shp 'save the new workbook to a specified folder With ActiveWorkbook .Sheets(1).Name = "Invoice" .SaveAs filename:=fname, FileFormat:=52 .Close End With End Sub Sub StartNewInvoice() Dim invno As Long invno = Range("F5") Range("B20,C20,B22:E32,B34:C34,D37,D39,E37,F4").ClearContents MsgBox "Your next invoice number is " & invno + 1 Range("F5") = invno + 1 Range("B13:C13").Select ThisWorkbook.Save End Sub
-
well organized!!, its actually the first one
Do you mean this:
If A3 in sheet1 is not blank AND Sheet1 Column K all rows in table are completely filled in then
- Copy Sheet1 A3 to Sheet2 B23
- Copy Sheet1 B3 to Sheet2 C23
- Copy Sheet1 C3 to Sheet2 E23
- Copy Sheet1 D3 to Sheet2 F23
- Copy Sheet1 G3 to Sheet2 B22
- Copy Sheet1 H3 to Sheet2 B13
- Copy Sheet1 L3 to Sheet2 B20
- Copy Sheet1 K3 to Sheet2 C20
Or this
If A3 in sheet1 is not blank then
- Copy Sheet1 A3 to Sheet2 B23
- Copy Sheet1 B3 to Sheet2 C23
- Copy Sheet1 C3 to Sheet2 E23
- Copy Sheet1 D3 to Sheet2 F23
- Copy Sheet1 G3 to Sheet2 B22
- Copy Sheet1 H3 to Sheet2 B13
And if Sheet1 Column K all rows in table are completely filled in then
- Copy Sheet1 G3 to Sheet2 B22
- Copy Sheet1 H3 to Sheet2 B13
-
I can now become a excel vba pro like you!!
-
hahaha yes, this is kinda crazy, have to word it carefully and may need to repeat the question more than once lol
-
tried it again, and it seems this code some what works
Code
Display MoreSub CopyText() Dim sourceSheet As Worksheet Dim targetSheet As Worksheet Dim sourceRange As Range Dim targetRange As Range Dim sourceRow As Long Dim targetRow As Long 'Set the source sheet and target sheet Set sourceSheet = Worksheets("Sheet1") Set targetSheet = Worksheets("Sheet2") 'Start at row 3 in the source sheet sourceRow = 3 'Loop through rows in the source sheet Do Until sourceSheet.Cells(sourceRow, 1).Value = "" 'Check if column E and K are filled in If sourceSheet.Cells(sourceRow, 5).Value <> "" And sourceSheet.Cells(sourceRow, 11).Value <> "" Then 'Set the source range and target range Set sourceRange = sourceSheet.Range("A" & sourceRow) Set targetRange = targetSheet.Range("B23") 'Copy the text from the source range to the target range targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("B" & sourceRow) Set targetRange = targetSheet.Range("C23") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("C" & sourceRow) Set targetRange = targetSheet.Range("E23") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("E" & sourceRow) Set targetRange = targetSheet.Range("F5") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("G" & sourceRow) Set targetRange = targetSheet.Range("B22") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("H" & sourceRow) Set targetRange = targetSheet.Range("B13") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("L" & sourceRow) Set targetRange = targetSheet.Range("B20") targetRange.Value = sourceRange.Text Set sourceRange = sourceSheet.Range("K" & sourceRow) Set targetRange = targetSheet.Range("C20") targetRange.Value = sourceRange.Text End If 'Move to the next row in the source sheet sourceRow = sourceRow + 1 Loop End Sub
-
so i tweaked the question
In excel How do I use VBA to copy text only in cells "A3" from sheet 1 to cell "B23" from sheet 2, cells "B3" from sheet 1 to cell "C23" from sheet 2, cell "C3" from sheet 1 to cell "E23" from sheet 2, cell "E3" from sheet 1 to cell "F5" from sheet 2, cell "G3" from sheet 1 to cell "B22" from sheet 2, cell "H3" from sheet 1 to cell "B13"from sheet 2, cell "L3" from sheet 1 to cell "B20" from sheet 2, cell "K3" from sheet 1 to cell "C20" from sheet 2 starting from row 3 from sheet 1 only if cell "E3" from sheet 1 is filled in and if column "K" from sheet 1 is filled in afterwards.
got this
Code
Display MoreSub CopyCells() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim rng As Range Set ws1 = ThisWorkbook.Sheets("Sheet1") Set ws2 = ThisWorkbook.Sheets("Sheet2") ' Check if cell "E3" in sheet1 is filled If Not IsEmpty(ws1.Range("E3")) Then ' Copy cell "A3" from sheet1 to cell "B23" in sheet2 ws1.Range("A3").Copy ws2.Range("B23") ' Copy cell "B3" from sheet1 to cell "C23" in sheet2 ws1.Range("B3").Copy ws2.Range("C23") ' Copy cell "C3" from sheet1 to cell "E23" in sheet2 ws1.Range("C3").Copy ws2.Range("E23") ' Copy cell "E3" from sheet1 to cell "F5" in sheet2 ws1.Range("E3").Copy ws2.Range("F5") ' Copy cell "G3" from sheet1 to cell "B22" in sheet2 ws1.Range("G3").Copy ws2.Range("B22") ' Copy cell "H3" from sheet1 to cell "B13" in sheet2 ws1.Range("H3").Copy ws2.Range("B13") End If ' Check if column "K" in sheet1 is filled Set rng = ws1.Range("K3:K" & ws1.Rows.Count) If Application.WorksheetFunction.CountA(rng) > 0 Then ' Copy cell "L3" from sheet1 to cell "B20" in sheet2 ws1.Range("L3").Copy ws2.Range("B20") ' Copy cell "K3" from sheet1 to cell "C20" in sheet2 ws1.Range("K3").Copy ws2.Range("C20") End If End Sub
but it didnt lol HAHAHAHAH
-
I know right, I didn't have time to test it today, I'll do soon tomorrow lol
-
lol I haven't tried it yet....CARIM!!!!!! Been really busy, well try later!!!
-
so you saying it was correct lol
-
so i tried asking chatbot this question and im not sure if this is right.....
In excel How do I use VBA to copy cells "C3" in sheet 1 to cell "E23" in sheet 2, cell "E3" in sheet 1 to cell "F5" in sheet 2, cell "G3" in sheet 1 to cell "B22" in sheet 2, cell "H3" in sheet 1 to cell "B13"in sheet 2, cell "I3" in sheet 1 to cell "B20" in sheet 2, cell "K3" in sheet 1 to cell "C20" in sheet 2 starting from row 3 from sheet 1 only if cell "E3" in sheet 1 is filled in and if column "K" in sheet 1 is filled in afterwards.
this is the code i got
Code
Display MoreSub CopyCells() 'Declare variables Dim ws1 As Worksheet Dim ws2 As Worksheet 'Set worksheets Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") 'Check if cell E3 in Sheet1 is filled in If ws1.Range("E3").Value <> "" Then 'Copy cell C3 from Sheet1 to cell E23 in Sheet2 ws2.Range("E23").Value = ws1.Range("C3").Value 'Copy cell E3 from Sheet1 to cell F5 in Sheet2 ws2.Range("F5").Value = ws1.Range("E3").Value 'Copy cell G3 from Sheet1 to cell B22 in Sheet2 ws2.Range("B22").Value = ws1.Range("G3").Value 'Copy cell H3 from Sheet1 to cell B13 in Sheet2 ws2.Range("B13").Value = ws1.Range("H3").Value 'Copy cell I3 from Sheet1 to cell B20 in Sheet2 ws2.Range("B20").Value = ws1.Range("I3").Value 'Check if cell K3 in Sheet1 is filled in If ws1.Range("K3").Value <> "" Then 'Copy cell K3 from Sheet1 to cell C20 in Sheet2 ws2.Range("C20").Value = ws1.Range("K3").Value End If End If End Sub
-
sorry didnt see this, yes im sure, i dont want you redoing everything
-
ok well this work without redoing all the macros??
*Bin Size (Column A) from row 3 , copies to the next available cell starting at cell 23 downwards to Invoice sheet (Column BC merged)
*Bin Price (Column C)from row 3 , copies to the next available cell starting at cell 23 downwards to Invoice sheet (Column E)
*Invoices (Column E) from row 3, copies Invoice sheet (Column F5)
*Extra Days (Column F) The Heading In "row 2" copies to the next available cell starting at cell 23downwards on Invoice sheet (Column BC merged)
*Extra Days (Column F) from row 3, copies to the next available cell starting at cell 23 downwards on Invoice sheet (Column D)
*Locations (Column G) from row 3, copies Invoice sheet (Column BC23 Merged)
*Customers (Column H) from row 3, copies Invoice sheet (Column BC13 Merged)
*Date Delivered (Column H) from row 3, copies Invoice sheet (Column B20)
*Date Delivered (Column H) from row 3, copies Invoice sheet (Column C20)
*Date Delivered (Column H) from row 3, copies Invoice sheet (Column F4)
-
so i might as well just keep the original format, and work with my invoice?
-
i know macros is a pain the the butt, and to re due them is a a pain in that butt
-
is that ok with you though?
-
Quote
You have attached a new file in Message #21 ... and it creates PLENTY of New Confusion !!!
The Main sheet shows a different structure ... and many new Columns have been inserted ... which will prevent all the macros from working properly ... !!!
yes i know, if its to much i can continue with the final sheet you completed for me. i saw that everything was completed upside down when i added knew columns
-
Regarding your two NEW questions ... it is better to go step by step ...
i agree
If you want to work with the AutoComplete feature in your dropdowns, you will need to insert an ActiveX ComboBox
no idea what that is lol
-
it worked!!!!!!!!!! thank you thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
i have 2 more tweaks and everything will be completed and help with saving time
this it it, this is the final version.......the end of the race.....at the end of the road TWEAK!!!!!!!!
1. the drop down menus i have, is it possible to have a search function instead of scrolling up and down? lets say im looking for bobby. when i type "bo", it will show the names related to those words "bob" or Bobby" and i can simply select it.
*So i basically rearranged the sheet and added some new headings and things are a are a bit off. wanted to make sure i set the stage so you can visually see it
for my second streamline idea, i will try and explain this the best i can
2. i will like to copy specific cell vales (not colors) from "main sheet" or "Outstanding Sheet" to my invoice sheet if there is an invoice number in column J and Date Collected (Column O) is selected afterwards
*Bin Size (Column A) from row 3 , copies to the next available cell starting in row 23 to Invoice sheet (Column BC merged)
*Bin Price (Column C)from row 3 , copies to the next available cell starting in row 23 to Invoice sheet (Column E)
*Extra Days (Column E) The Heading In "row 2" copies to the next available cell starting in row 23 on Invoice sheet (Column BC merged)
*Extra Days (Column E) from row 3, copies to the next available cell starting in row 23 on Invoice sheet (Column D)
*Extra Days Price (Column F) from row 3, copies to the next available cell starting in row 23 on Invoice sheet (Column E)
*Equipment/Material (Column G) from row 3 , copies to the next available cell starting in row 23 on Invoice sheet (Column BC merged)
*Material Prices (Column H) from row 3, copies to the next available cell starting in row 23 on Invoice sheet (Column E)
*Delivery Prices (Column I) from row 3, copies to the next available cell starting in row 23 on Invoice sheet (Column E)
-