VBA Code to Keep Formatting

[Glasgowsmile]

Good Morning,

I've got the following code below to pull data from another spreadsheet. It works fine, no issues but I want the data to look exactly the same in the current workbook I'm using when I pull the data over. How do I keep the formatting consistent so it doesn't expand columns/rows etc?

Sub PastPDI()   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Sheets("Property Segment Data").Range("A1:Z40").Copy wkbCrntWorkBook.Sheets("Sheet1").Range("A1")
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").CurrentRegion.EntireColumn.AutoFit
         wkbSourceBook.Close False
      End If
   End With
End Sub

 

[Glasgowsmile]

Additionally, I discovered this is copying the Formula from the other sheet instead of just the Data. How do I keep the formatting and copy just the data (not the formulas)?

 

[Glasgowsmile]

Based on what I've read, I need to add PasteSpecial to this code but where should I add it?

 

[Norie]

So you want to copy values and formats, but not formulas?

Try this.

Sheets("Property Segment Data").Range("A1:Z40").Copy 

wkbCrntWorkBook.Sheets("Sheet1").Range("A1").PasteSpecial  xlPasteValues
wkbCrntWorkBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats

PS If it's only number formats you want to retain this might work.

wkbCrntWorkBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats

 

[Glasgowsmile]

How would I change the height and width of rows and columns after I import the data?

I tried adding this column one to the bottom but doesn't seem to work.

Sub PastPDI()

   Sheets("Sheet1").Cells.ClearContents
   Dim wkbCrntWorkBook As Workbook
   Dim wkbSourceBook As Workbook
   Set wkbCrntWorkBook = ActiveWorkbook
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set wkbSourceBook = ActiveWorkbook
         Sheets("Property Segment Data").Range("B2:Z40").Copy
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteValues
         wkbCrntWorkBook.Sheets("Sheet1").Range("A1").PasteSpecial xlPasteFormats
         wkbSourceBook.Close False
      End If
   End With
   
   Columns("B").ColumnWidth = 23
End Sub