Next Invoice Number #1505

[gurolk]

Hi,

I need to register invoice data to new workbook. Can you help me please.

 

[Joe4]

Welcome to the Board!

Can you help me please.

Not from what you have posted. You haven't given us any details to work from. Remember, we have no idea what your sheet/data/or anything else looks like. All we have to go on is what you provide to us here.

Please read this thread on guidelines to posting a good question that allows us to help you.
https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[gurolk]

Hi Joe04,
Code below write data to same workbook. I need to write data to different workbook.


Sub PostToRegister()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Quota")
Set WS2 = Worksheets("Register")

'Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Write the important values to Register
WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("F7"), WS1.Range("c5"), WS1.Range("f5"), WS1.Range("c6"))

End Sub

 

[Joe4]

Is the "different workbook" you need to write to known ahead of time, where we can "hard-code" the file path and name into the VBA code?
Or, do you need the ability to browse/select the workbook it should be written to?

What are the details of the path/filename?

 

[gurolk]

f:\aaa\teklif\takip.xlsx

 

[Joe4]

OK, here's a little demonstration macro to show you how you can set workbook variables to control two separate workbooks, how to open your other file in the code, and how to bounce back and forth by selecting the workbook objects:

Sub MyMacro()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim fname As String
    
'   Set current macro workbook to wb1 object variable
    Set wb1 = ActiveWorkbook
    
'   Set full path and file name of file that you want to open
    fname = "f:\aaa\teklif\takip.xlsx"
    
'   Open other workbook
    Workbooks.Open Filename:=fname
    
'   Set this new workbook to to the wb2 object variable
    Set wb2 = ActiveWorkbook
    
'   Go back to original workbook
    wb1.Activate
    
End Sub

 

[gurolk]

Thanks for your kindly support. But it's so hard to use this codes for me. Codes below works at the same workbook. Would you please separate "Teklif" and "Register" worksheet different workbooks

Sub PostToRegister()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Teklif")
Set WS2 = Worksheets("Register")

'Figure out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

'Write the important values to Register
WS2.Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("F7"), WS1.Range("f5"), _
WS1.Range("c6"), WS1.Range("c5"), WS1.Range("f8"), WS1.Range("I2"))


End Sub

 

[Joe4]

Are both workbooks already open when you want to run the code?
If one of these workbooks the one where the VBA code is going to reside? If so, then which one?

 

[gurolk]

"Teklif" Workbook open and VBA codes are on it. Other workbook not open.

 

[Joe4]

Try this:

Sub PostToRegister()

    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim fname As String
    Dim arr
    Dim NextRow As Long

'   Set current macro workbook to wb1 object variable
'   (takip workbook is the active workbook where this VBA code resides
    Set wb1 = ActiveWorkbook
    
'   Capture values from this takip workbook in array
    arr = Array(Range("F7"), Range("F5"), Range("C6"), _
        Range("C5"), Range("F8"), Range("I2"))
    
'   Set full path and file name of registerfile that you want to open
[COLOR=#ff0000]    fname = "f:\aaa\teklif\register.xlsx"[/COLOR]
    
'   Open other workbook
    Workbooks.Open Filename:=fname
    
'   Set the register workbook to to the wb2 object variable
    Set wb2 = ActiveWorkbook
    
'   Figure out which row is the next row
    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

'   Write the important values to Register
    Cells(NextRow, 1).Resize(1, 6).Value = arr
    
'   Save and close register workbook
    ActiveWorkbook.Save
    wb2.Close
    
End Sub

Note that you may need to change the line in red to match exactly where this "Register" workbook resides on your network.