Trying2learnVBA
Board Regular
- Joined
- Aug 21, 2019
- Messages
- 67
- Office Version
- 365
- 2021
- Platform
- Windows
Hello,
I can't get my code to work. My source workbook that will hold my macros has a tab name Invoices_src. I open the template I want to autofill called extVIOL1.
This works - create the folders
On Error Resume Next
MkDir (Environ("USERPROFILE") & "\Desktop\N_Invoices\")
MkDir (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates")
On Error GoTo 0
''''''''''''''''''''''
'Open Template
''''''''''''''''''''''
This works
Workbooks.Open (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\extVIO1.xlsx")
Application.ScreenUpdating = False
Here I am trying to say - I want my template to be filled with up to the last empty row in my src wb
'LR = Invoices_src.Cells(Invoices_src.Rows.Count, 1).End(xlUp).Row
'If Invoices_src.Range("C6:C") > 1 Then
'For Each Cel In Invoices_src.Range("C6:C").Cells
'
This is what I want to happen - Template extVIO1 should be filled out from A4 to the last row with info from my source file. and so on for col B, C, D, E, F. Once the template is filled out
I would like the extVIO1 file to saveas in folder "\Desktop\N_Invoices\Quadrates") named as Datetoday KO01
extVIO1.Range("A4:A").Value = Invoices_src.Range("V" & Cel.Row)
'CGCode
extVIO1.Range("B" & LR).Value = Invoices_src.Range("L" & Cel.Row)
'Sector
Range("C" & LR).Value = Invoices_src.Range("M" & Cel.Row)
'CCtr
Range("D" & LR).Value = Invoices_src.Range("M" & Cel.Row)
'Est Cost
Range("E" & LR).Value = Round(Invoices_src.Range("K" & Cel.Row), 2)
Range("F" & LR).Value = "'20"
'Next Cel
' With extVIO1
Here I was trying to use some code I found but I can't get it to work for my purpose
'rootFolder = CreateObject("scripting.FileSystemObject").GetParentFolderName(ThisWorkbook.FullName)
''exportFolder = rootFolder & "\Export\"
'exportFolder = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\"
'templateFolder = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\"
''timestamp = Validations.Range("T1").Value
'dateToday = Format(Now(), "mm/dd/yyyy")
''InvoicesLR = Invoices.Cells(Invoices.Rows.Count, 1).End(xlUp).Row
'InvoicesLR = Invoices.Cells(Invoices.Rows.Count, 1).End(xlUp).Row
'''''''''''''''
'Fill out KO01'
'''''''''''''''
'Invoices
I tried this but I seem unable to set variable LR
'If LR > 1 Then
' For Each Cel In Invoices.Range("C6:C" & LR).Cells
' extLR = extVIO1.Cells(extVIO1.Rows.Count, 1).End(xlUp).Row + 1
' extVIO1.Range("A" & extLR).Value = "VIOL"
' 'CGCode
' extVIO1.Range("B" & extLR).Value = scrInvoices.Range("L" & Cel.Row)
' 'Sector
' extVIO1.Range("C" & extLR).Value = scrInvoices.Range("M" & Cel.Row)
' 'CCtr
' extVIO1.Range("D" & extLR).Value = scrInvoices.Range("M" & Cel.Row)
' 'Est Cost
' extVIO1.Range("E" & extLR).Value = Round(srcInvoices.Range("K" & Cel.Row), 2)
' extVIO1.Range("F" & extLR).Value = "'20"
Thank you in advance for any tips and guidance!
I can't get my code to work. My source workbook that will hold my macros has a tab name Invoices_src. I open the template I want to autofill called extVIOL1.
This works - create the folders
On Error Resume Next
MkDir (Environ("USERPROFILE") & "\Desktop\N_Invoices\")
MkDir (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates")
On Error GoTo 0
''''''''''''''''''''''
'Open Template
''''''''''''''''''''''
This works
Workbooks.Open (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\extVIO1.xlsx")
Application.ScreenUpdating = False
Here I am trying to say - I want my template to be filled with up to the last empty row in my src wb
'LR = Invoices_src.Cells(Invoices_src.Rows.Count, 1).End(xlUp).Row
'If Invoices_src.Range("C6:C") > 1 Then
'For Each Cel In Invoices_src.Range("C6:C").Cells
'
This is what I want to happen - Template extVIO1 should be filled out from A4 to the last row with info from my source file. and so on for col B, C, D, E, F. Once the template is filled out
I would like the extVIO1 file to saveas in folder "\Desktop\N_Invoices\Quadrates") named as Datetoday KO01
extVIO1.Range("A4:A").Value = Invoices_src.Range("V" & Cel.Row)
'CGCode
extVIO1.Range("B" & LR).Value = Invoices_src.Range("L" & Cel.Row)
'Sector
Range("C" & LR).Value = Invoices_src.Range("M" & Cel.Row)
'CCtr
Range("D" & LR).Value = Invoices_src.Range("M" & Cel.Row)
'Est Cost
Range("E" & LR).Value = Round(Invoices_src.Range("K" & Cel.Row), 2)
Range("F" & LR).Value = "'20"
'Next Cel
' With extVIO1
Here I was trying to use some code I found but I can't get it to work for my purpose
'rootFolder = CreateObject("scripting.FileSystemObject").GetParentFolderName(ThisWorkbook.FullName)
''exportFolder = rootFolder & "\Export\"
'exportFolder = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\"
'templateFolder = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\"
''timestamp = Validations.Range("T1").Value
'dateToday = Format(Now(), "mm/dd/yyyy")
''InvoicesLR = Invoices.Cells(Invoices.Rows.Count, 1).End(xlUp).Row
'InvoicesLR = Invoices.Cells(Invoices.Rows.Count, 1).End(xlUp).Row
'''''''''''''''
'Fill out KO01'
'''''''''''''''
'Invoices
I tried this but I seem unable to set variable LR
'If LR > 1 Then
' For Each Cel In Invoices.Range("C6:C" & LR).Cells
' extLR = extVIO1.Cells(extVIO1.Rows.Count, 1).End(xlUp).Row + 1
' extVIO1.Range("A" & extLR).Value = "VIOL"
' 'CGCode
' extVIO1.Range("B" & extLR).Value = scrInvoices.Range("L" & Cel.Row)
' 'Sector
' extVIO1.Range("C" & extLR).Value = scrInvoices.Range("M" & Cel.Row)
' 'CCtr
' extVIO1.Range("D" & extLR).Value = scrInvoices.Range("M" & Cel.Row)
' 'Est Cost
' extVIO1.Range("E" & extLR).Value = Round(srcInvoices.Range("K" & Cel.Row), 2)
' extVIO1.Range("F" & extLR).Value = "'20"
Thank you in advance for any tips and guidance!