foolishpiano
New Member
- Joined
- Aug 19, 2016
- Messages
- 28
Hi Everyone!
I have an import macro that I would like some help with, please. I would like to retain the formatting of the cell(s) and text(s) from my old workbook into the new workbook I am importing into. Is there a way to do this?
The code I am working with is:
I'm not sure if something like this is possible or not, but I'm willing to bet that someone here does.
Thank you all for your help!
I have an import macro that I would like some help with, please. I would like to retain the formatting of the cell(s) and text(s) from my old workbook into the new workbook I am importing into. Is there a way to do this?
The code I am working with is:
Code:
Sub Import()
' Turn Screen Updating Off
Application.ScreenUpdating = False
'make cell N1 formatted as Text
Range("N1").NumberFormat = "@"
' Get customer workbook...
Dim customerBook As Workbook
Dim filter As String
Dim caption As String
Dim customerFilename As String
Dim customerWorkbook As Workbook
Dim targetWorkbook As Workbook
' make weak assumption that active workbook is the target
Set targetWorkbook = Application.ActiveWorkbook
' get the old workbook
filter = "Excel Workbooks (*.xls),*.xls"
caption = "Please Select an Input File "
On Error GoTo Canceled
ChDrive "U:"
ChDrive "U:\Compliance\Files In Process\" & Range("bx73") & "\"
customerFilename = Application.GetOpenFilename(filter, , caption)
Set customerWorkbook = Application.Workbooks.Open(customerFilename)
' assume range is A1 - AI33 in sheet1
' copy data from customer to target workbook
Dim targetSheet As Worksheet
Set targetSheet = targetWorkbook.Worksheets(1)
Dim sourceSheet As Worksheet
Set sourceSheet = customerWorkbook.Worksheets(1)
targetSheet.Range("N1", "N1").Value = sourceSheet.Range("N1", "N1").Value
targetSheet.Range("W1", "W1").Value = sourceSheet.Range("W1", "W1").Value
targetSheet.Range("F5", "F5").Value = sourceSheet.Range("F5", "F5").Value
targetSheet.Range("V5", "AD7").Value = sourceSheet.Range("V5", "AD7").Value
targetSheet.Range("AI5", "AI7").Value = sourceSheet.Range("AI5", "AI7").Value
targetSheet.Range("F10", "F16").Value = sourceSheet.Range("F10", "F16").Value
targetSheet.Range("N12", "N13").Value = sourceSheet.Range("N12", "N13").Value
targetSheet.Range("N15", "N15").Value = sourceSheet.Range("N15", "N15").Value
targetSheet.Range("T10", "AD16").Value = sourceSheet.Range("T10", "AD16").Value
targetSheet.Range("S17", "S17").Value = sourceSheet.Range("S17", "S17").Value
targetSheet.Range("A23", "H23").Value = sourceSheet.Range("A23", "H23").Value
targetSheet.Range("A25", "H25").Value = sourceSheet.Range("A25", "H25").Value
targetSheet.Range("A27", "H27").Value = sourceSheet.Range("A27", "H27").Value
targetSheet.Range("A29", "H29").Value = sourceSheet.Range("A29", "H29").Value
targetSheet.Range("A31", "H31").Value = sourceSheet.Range("A31", "H31").Value
targetSheet.Range("A33", "H33").Value = sourceSheet.Range("A33", "H33").Value
' Save and Close old workbook
customerWorkbook.Save
customerWorkbook.Close
'Turn Screen Updating On
Application.ScreenUpdating = True
ActiveWindow.WindowState = xlMaximized
Canceled:
End SubI'm not sure if something like this is possible or not, but I'm willing to bet that someone here does.
Thank you all for your help!
