Keeping formatting while transferring info from one workbook to another VBA - Excel 2019

[netshadoe]

Good morning,

I have a script that I'm using to transfer information from a sheet (Variants) in a closed workbook to a sheet (Original Converge Results File) in the active workbook that I have open.
It works with no issues as it is, but one of the users of this tool I created asked if it was possible to import the information into the active document while keeping the formatting.

Here is my code - the actual information transfer code starts at "NGS Original Sample Variant Import Dialog Box" in the snippet below. I'm not sure where I could add any options to keep original formatting.
This would be for EXCEL 2019.

Would anyone with more VBA knowledge have any ideas - still learning this coding thing!!

Thanks!

Sub Import_NGS_Results()

Dim Filename As Variant
Dim SourceWB As Workbook
Dim DestWB As Workbook
Dim Scientist As Variant
Dim NON As Variant
Dim STACSID As Variant
Dim Reference As Variant
Dim dtToday As Date
Dim chDate As Date
Dim i As Integer, j As Integer, a As Integer, b As Integer

dtToday = Format(Date, "yyyy-mm-dd")

'Query Scientist Name
Scientist = InputBox("Please enter scientist name:", "Scientist")
    If StrPtr(Scientist) = 0 Then
        Exit Sub
    ElseIf Scientist = vbNullString Then
        Exit Sub
    Else
        Range("B3").Value = Scientist
    End If

'Query NON
NON = InputBox("Please enter National Occurence Number:", "National Occurence Number")
Range("B1").Value = NON

'Query STACS ID
STACSID = InputBox("Please enter STACS ID:", "STACS ID")
Range("B2").Value = STACSID

'Query Interpretation Date
chDate = InputBox("Please enter Interpretation Date:", "Interpretation Date", dtToday)
Range("I3").Value = chDate

'NGS Original Sample Variant Import Dialog Box
Filename = Application.GetOpenFilename(Title:="Select the Original Converge Results File", FileFilter:="Excel Files,*.xlsx")
If Filename = False Then Exit Sub
Filename = Mid(Filename, InStrRev(Filename, "\") + 1, 999)

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set DestWB = ActiveWorkbook
Set SourceWB = Workbooks.Open(Filename)

DestWB.Sheets("Original Converge Results File").Activate
SourceWB.Activate

i = 1 'Original Converge Results File Import Row
j = 1 'Original Converge Results File Import Col

For a = 1 To SourceWB.Sheets("Variants").Range("A65536").End(xlUp).Row ' Import rows
    For b = 1 To 30 'Import Columns
        DestWB.Sheets("Original Converge Results File").Cells(i, j) = SourceWB.Sheets("Variants").Cells(a, b) 'Data
        j = j + 1
    Next
    i = i + 1
    j = 1
Next

SourceWB.Close savechanges:=False

Sheet1.Select
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic 'Set Calculation to Automatic

End Sub

 

[Anthony47]

Here is my code - the actual information transfer code starts at "NGS Original Sample Variant Import Dialog Box" in the snippet below. I'm not sure where I could add any options to keep original formatting.

You should use Copy & PasteSpecial; so replace the whole For a = 1 To SourceWB.Sheets("Variants").Range("A65536").End(xlUp).Row / Next[a] loop with

Dim LastA As Long

LastA = SourceWB.Sheets("Variants").Range("A65536").End(xlUp).Row
'Copy source area:
SourceWB.Sheets("Variants").Range("A1").Resize(LastA, 30).Copy
'PasteSpecial to DestWB:
DestWB.Sheets("Original Converge Results File").Range("A1").PasteSpecial xlPasteValues
DestWB.Sheets("Original Converge Results File").Range("A1").PasteSpecial xlPasteFormats
'***** Here other pastespecials, if necessary
Application.CutCopyMode = False

The code continue with SourceWB.Close savechanges:=False

In case you need to copy more formats (column width, comments,...) you may add more PasteSpecial in the line marked ****; see XlPasteType enumeration (Excel) for the available options

 

[netshadoe]

Thanks a lot @Anthony47 . That worked perfectly. I figured that the original code was way too complicated!! Your code is simpler and much more eligant!

Thanks!!

Devin

 

[Anthony47]

Your code is simpler and much more eligant!

Let's say that "it works", and I feel satisfied