What's wrong with my VBA Code

[Trying2learnVBA]

Hello,

I am trying to fill out WB2 with data from WB1 which houses the macro - the code runs but nothing gets filled out.

Dim WB1 As Workbook -
Dim WB2 As Workbook
Dim LastRow As Long

Application.ScreenUpdating = False

Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\extKO01.xlsx")

With WB2.Sheets("KO01_IO")
LastRow = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > 3 Then
.Range("A4:AW" & LastRow).ClearContents
End If
End With
This works - I simply want WB2 to not have any unwanted data.

With WB1.Sheets("N_Invoices")
'Find the last cell's row with data in any column
LastRow = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Could use .Cells(.Rows.Count, 1).End(xlUp).Row
'Copy the values
WB2.Sheets("KO01_IO").Range("A4" & LastRow) = .Range("V6" & LastRow).Value
End With
WB2.Save

Nothing happens here - WB2 should have column A filled out with everything in WB1 colum V6 to last row.

End Sub

I am not getting any error here - so I am not sure if I have anything backwards.

Thank you

 

[Dan Waterloo]

Hi Trying,

Your ranges do not look like actual ranges. If LastRow is 2000 then Range("A4"&LastRow) will point to the single cell A42000. The copy operation is likely copying one blank cell to another. That's why you are not getting an error message.

 

[Trying2learnVBA]

Hi Trying,

Your ranges do not look like actual ranges. If LastRow is 2000 then Range("A4"&LastRow) will point to the single cell A42000. The copy operation is likely copying one blank cell to another. That's why you are not getting an error message.

thank you - this helped. Now I am getting two additional rows. I am not sure why.

I want to copy everything on WB1 from row 6 to the last row.

With WB1.Sheets("N_Invoices")
'Find the last cell's row with data in any column
LastRow = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Could use .Cells(.Rows.Count, 1).End(xlUp).Row
'Copy the values
If LastRow > 6 Then (Should this be 5?)

WB2.Sheets("KO01_IO").Range("A4:A" & LastRow) = .Range("V6:V" & LastRow).Value - This copied the 46 rows that I want plus it added two more that say #N/A.

End If
End With

WB2.Save

End Sub

 

[Dan Waterloo]

I think you want an AW in there:
WB2.Sheets("KO01_IO").Range("A4:AW" & LastRow)

The range is defined the two corner cells of the range: A4 and AW2000 (or whatever the last row actually is).

 

[Trying2learnVBA]

I am getting an extra two rows that say #N/A
I tried it without the If LastRow > 6 line of code and it does the samething.

This is the extra stuff I am getting

VIOL	CG52	FLIK	26259​	23935.17​	20
VIOL	#N/A​	#N/A​	#N/A​	#N/A​	20
VIOL	#N/A​	#N/A​	#N/A​	#N/A​	20

My Current Code
Sub KO_01()
Dim WB1 As Workbook
Dim WB2 As Workbook
Dim LastRow As Long

Application.ScreenUpdating = False

Set WB1 = ActiveWorkbook
Set WB2 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\extKO01.xlsx")

With WB2.Sheets("KO01_IO")
LastRow = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

If LastRow > 3 Then
.Range("A4:AW" & LastRow).ClearContents
End If
End With

With WB1.Sheets("N_Invoices")
'Find the last cell's row with data in any column
LastRow = .Range("A:U").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'Could use .Cells(.Rows.Count, 1).End(xlUp).Row
'Copy the values
'If LastRow > 6 Then


WB2.Sheets("KO01_IO").Range("A4:A" & LastRow) = "VIOL"
'CGCode
WB2.Sheets("KO01_IO").Range("B4:B" & LastRow) = .Range("L6:L" & LastRow).Value
'Sector
WB2.Sheets("KO01_IO").Range("C4:C" & LastRow) = .Range("M6:M" & LastRow).Value
'CCtr
WB2.Sheets("KO01_IO").Range("D4:D" & LastRow) = .Range("C6:C" & LastRow).Value
'Est Cost
WB2.Sheets("KO01_IO").Range("E4:E" & LastRow) = .Range("K6:K" & LastRow).Value
'Investment Reason
WB2.Sheets("KO01_IO").Range("F4:F" & LastRow) = "'20"


'End If
End With

WB2.Save (Can somebody please tell me what's the code to do a SaveAs and save it to (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\)

End Sub

 

[Alex Blakenburg]

You are using the same LastRow variable for both workbooks.
When you use .value = .value the 2 ranges need to match exactly in size. By using the same LastRow you have one sized from row 4 to LastRow and one from row 6 to LastRow and hence one is 2 rows more.

Trying to make as few changes to your code as possible try the below:

Sub KO_01_Modified()
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    Dim LastRow2 As Long
    Dim FirstRow1 As Long, LastRow1 As Long, NoOfRows1 As Long
   
    Application.ScreenUpdating = False
   
    Set WB1 = ActiveWorkbook
    Set WB2 = Workbooks.Open(Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrate Templates\extKO01.xlsx")
   
    With WB2.Sheets("KO01_IO")
        LastRow2 = .Range("A:AW").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
        If LastRow2 >= 4 Then
            .Range("A4:AW" & LastRow2).ClearContents
        End If
    End With
       
    With WB1.Sheets("N_Invoices")
        'Find the last cell's row with data in any column
        LastRow1 = .Range("A:U").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        FirstRow1 = 6
        NoOfRows1 = LastRow1 - FirstRow1 + 1
        'Could use .Cells(.Rows.Count, 1).End(xlUp).Row
        'Copy the values
        If LastRow >= 6 Then
           
            WB2.Sheets("KO01_IO").Range("A4").Resize(NoOfRows1) = "VIOL"
            'CGCode
            WB2.Sheets("KO01_IO").Range("B4").Resize(NoOfRows1) = .Range("L6:L" & LastRow1).Value
            'Sector
            WB2.Sheets("KO01_IO").Range("C4").Resize(NoOfRows1) = .Range("M6:M" & LastRow1).Value
            'CCtr
            WB2.Sheets("KO01_IO").Range("D4").Resize(NoOfRows1) = .Range("C6:C" & LastRow1).Value
            'Est Cost
            WB2.Sheets("KO01_IO").Range("E4").Resize(NoOfRows1) = .Range("K6:K" & LastRow1).Value
            'Investment Reason
            WB2.Sheets("KO01_IO").Range("F4").Resize(NoOfRows1) = "'20"
       
        End If
    End With
   
    'WB2.Save (Can somebody please tell me what's the code to do a SaveAs and save it to (Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\)

End Sub

Also please use the code buttons to post your code. The VBA button or if you want to format the code the RICH Button.

 

[Alex Blakenburg]

I think your last question may be covered in your previous post
VBA to open a workbook and fill it out from a source file
but see if this helps:

    Dim fName As String, pathName As String, fullName As String
    fName = "Quadrate Templates\extVIO1.xlsx"               ' <--- Change this to your file name or cell with filename
    pathName = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\"
    fullName = pathName & fName

    WB2.SaveAs Filename:=fullName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

 

[Trying2learnVBA]

I think your last question may be covered in your previous post
VBA to open a workbook and fill it out from a source file
but see if this helps:

    Dim fName As String, pathName As String, fullName As String
    fName = "Quadrate Templates\extVIO1.xlsx"               ' <--- Change this to your file name or cell with filename
    pathName = Environ("USERPROFILE") & "\Desktop\N_Invoices\Quadrates\"
    fullName = pathName & fName

    WB2.SaveAs Filename:=fullName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Hey for fName - I simply want to use Today's date and KO01. To do this I joined a date and KO01 in WB1 Tab "Validations" Cell P1

However, I am not quite sure as to what is the proper way to type the code.
fName = Validations.Range("P1")

 

[Alex Blakenburg]

If you have given the worksheet the code name of Validations, then what you have got should work.
If not change it to

fName = Worksheets("Validations").Range("P1")

 

[Trying2learnVBA]

If you have given the worksheet the code name of Validations, then what you have got should work.
If not change it to

fName = Worksheets("Validations").Range("P1")

Thank you!!! (My code had ended my "With" too soon, I put End With at the end. This worked.

BTW - I modified WB1 so the first empty row is the same as WB2. This allowed my original code to work.
Another fix was to do: WB2.Sheets("KO01_IO").Range("A4:A" & LastRow - 2) = "VIOL"

Thanks to your help - my first module is complete.
Two more to go but they should be pretty similar.

I am creating an Internal Order Template (SAP) KO01
Next Step is to create assets (SAP) AS01
and finally to book the accounting entry - (SAP) F-51.

So, the next two quadrates should pretty much use the same code but with different ranges to fill out.

Thank you again!