How to copy data from single cells to merge cells and vice versa

[kamranyd]

i have these codes which copy data from data sheet to quot sheet was working fine, but now my quot sheet has few merge cells now it dont copy. does merge cells dont copy values from single cells sheets?

Sub search_quot()
    Dim Quot_No As String, Fnd As Range, Ws1 As Worksheet, Ws2 As Worksheet, FileNameRef As String, X As Long, Y As Long: Y = 17
    
    Sheets("Quot").Unprotect
    Application.ScreenUpdating = False
    
    Set Ws1 = Sheets("database")
    Set Ws2 = Sheets("QUOT")
    
    FileNameRef = Range("I3").Value
    
    Set Fnd = Ws1.Range("A:A").Find(Ws2.Range("I3").Value, , , xlWhole, , , , , False)
    If Fnd Is Nothing Then MsgBox "Quotation Number [-]> " & FileNameRef & " <[-]" & vbNewLine & "Not found in Quotation Database.", vbExclamation, "Quote Search ERROR": Exit Sub
    
    With Ws2
        .Range("I4").Value = Fnd.Offset(, 1).Value
        .Range("I5").Value = Fnd.Offset(, 128).Value
        .Range("C8").Value = Fnd.Offset(, 2).Value
        .Range("C9").Value = Fnd.Offset(, 3).Value
        .Range("A49").Value = Fnd.Offset(, 129).Value
        .Range("A51").Value = Fnd.Offset(, 130).Value
        .Range("H61").Value = Fnd.Offset(, 7).Value
             For X = 8 To 124 Step 4
            .Range("A" & Y).Resize(, 4).Value = Array(Fnd.Offset(, X).Value, Fnd.Offset(, X + 1).Value, Fnd.Offset(, X + 2).Value, Fnd.Offset(, X + 3).Value)
            Y = Y + 1
        Next X
    End With
     Range("A17").Select
     ActiveWindow.ScrollRow = 1
    'Sheets("Quot").Protect AllowFormattingCells:=True
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

Which macro is slow ?
And when is it slow, there doesn't seem to be enough data in your sample spreadsheet for it to be all that slow, since it only seems to do 1 invoice at a time,
If its the actual save that is slow then there is not a huge amount you can do about that. In your code it looks like it is saving to you C drive which should rule out network issues.
Is the actual workbook with the code on a Network drive or OneDrive or Sharepoint and do you have autosave turned on ? If this is the case try turning autosave off.

 

[kamranyd]

Which macro is slow ?
And when is it slow, there doesn't seem to be enough data in your sample spreadsheet for it to be all that slow, since it only seems to do 1 invoice at a time,
If its the actual save that is slow then there is not a huge amount you can do about that. In your code it looks like it is saving to you C drive which should rule out network issues.
Is the actual workbook with the code on a Network drive or OneDrive or Sharepoint and do you have autosave turned on ? If this is the case try turning autosave off.

no there is no auto save turn on. these codes save 1 invoice at a time. its a sample sheet so i removed all data from database sheet. if you don't mind, if you have to write these code or rearrange these codes, which will execute more quick and short, but purpose of this sheet remain same.

 

[Alex Blakenburg]

I'm afraid that is a different question and needs a new thread. While there may be improvements that can be made to the code, I think the bottle neck is the creation of PDF file and I can't see any way of speeding that up.

 

[kamranyd]

I'm afraid that is a different question and needs a new thread. While there may be improvements that can be made to the code, I think the bottle neck is the creation of PDF file and I can't see any way of speeding that up.

pdf file is quick when invoice is made, rest saving data to database is slow but it save all the the data. yes just want to know if more improvement can be made. ok i will look forward to make new thread. thnks

 

[Alex Blakenburg]

If you are talking about the "sub qaddata", when I comment out the checking for the file name and Exporting to the pdf there is barely a noticeable dela. So when you create a new thread you will need to be much more specific on where you think the delay is and how you are noticing it.