help on paste values vba please

[orsm6]

Hi all - i was given this sheet (which works) however what i'd like to do is just paste the values this code copies so i can keep the destination sheet formatting as is... this code stops at the active.sheet paste line, how can i modify this? TIA

Private Sub CopyTXT(destSht As Worksheet, sourceWbk As Workbook)

Set destinationWorkbook = ActiveWorkbook

Dim lastRow As Integer
Dim lastColumn As Integer

lastRow = LastRowNumber(sourceWbk.Sheets(1))
lastColumn = lastColNumber(sourceWbk.Sheets(1))

'copy the data
sourceWbk.Activate
sourceWbk.Sheets(1).Range(Cells(1, 1), Cells(lastRow, lastColumn)).Copy

'Open the sheet you're pasting into
destinationWorkbook.Activate
destSht.Activate

'Pick where to paste
lastRow = LastRowNumber(destSht)
Cells(lastRow + 1, 1).Select
ActiveSheet.PasteSpecial xlPasteValues

'clear the clipboard
Application.CutCopyMode = False

'close the current file
sourceWbk.Close


End Sub

 

[MARK858]

Assuming that LastRowNumber is a separate function maybe....

lastRow =  LastRowNumber(destSht)
destSht.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues

Although personally I would name the 2 lastRow variables differently for each sheet. I would also change all the Integers for Longs.

 

[orsm6]

Assuming that LastRowNumber is a separate function maybe....

lastRow =  LastRowNumber(destSht)
destSht.Cells(lastRow + 1, 1).PasteSpecial xlPasteValues

Although personally I would name the 2 lastRow variables differently for each sheet. I would also change all the Integers for Longs.

Hi Mark, this works perfectly thanks.

Our VBA skills are pretty limited and rely on google etc a lot lol... i am sure if someone with far better understanding looked at the entire set of Macros they would probably gasp in horror haha

 

[MARK858]

You're welcome, the code below is how I would adapt the code that you posted....

Private Sub CopyTXT(destSht As Worksheet, sourceWbk As Workbook)
    Dim lastRow As Long, LastRowDest As Long
    Dim lastColumn As Long

    lastRow = LastRowNumber(sourceWbk.Sheets(1))
    lastColumn = lastColNumber(sourceWbk.Sheets(1))

    'copy the data
    With sourceWbk.Sheets(1)
        .Range(.Cells(1, 1), .Cells(lastRow, lastColumn)).Copy
    End With

    'Pick where to paste
    LastRowDest = LastRowNumber(destSht)
    destSht.Cells(LastRowDest + 1, 1).PasteSpecial xlPasteValues

    'clear the clipboard
    Application.CutCopyMode = False

    'close the current file
    sourceWbk.Close


End Sub