Pasting Data without shifting Down rows
 

Hi, below is the code for importing a file into 'Imported Data'

Problem arises when the file is imported, references to the file in another
Worksheet (even stored as $constants) shift downward. The imported file
currently has 70 records, so if a new file is imported, the references shift
each time by 70 rows, therefore screwing up the formula results.

How can I clear the sheet, and paste the data other than using the
Selection.Insert Shift:=xlDown function?

Sub Import_Data()
' Import_Data Macro recorded 14/02/2007 by (me)
' This macro imports the data from the selected file output from the database
' This is limited to *.xls (default) or *.csv file formats

Dim oWB

Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
'ActiveWorksheet.Unprotect
Selection.CurrentRegion.Clear


With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values (*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")

Workbooks.Open FileToOpen
Set oWB = Workbooks.Open(FileToOpen)
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
oWB.Close

End With
'ActiveWorksheet.Protect
Sheets("Main").Activate
End Sub

Pasting Data without shifting Down rows
 

Don't worry, I fixed it -
Replace: Selection.Insert Shift:=xlDown
With ActiveSheet.Paste Destination:=Worksheets("Imported Data").Range("A1")

"D Zandveld" wrote:

Hi, below is the code for importing a file into 'Imported Data'

Problem arises when the file is imported, references to the file in another
Worksheet (even stored as $constants) shift downward. The imported file
currently has 70 records, so if a new file is imported, the references shift
each time by 70 rows, therefore screwing up the formula results.

How can I clear the sheet, and paste the data other than using the
Selection.Insert Shift:=xlDown function?

Sub Import_Data()
' Import_Data Macro recorded 14/02/2007 by (me)
' This macro imports the data from the selected file output from the database
' This is limited to *.xls (default) or *.csv file formats

Dim oWB

Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
'ActiveWorksheet.Unprotect
Selection.CurrentRegion.Clear


With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values (*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")

Workbooks.Open FileToOpen
Set oWB = Workbooks.Open(FileToOpen)
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
oWB.Close

End With
'ActiveWorksheet.Protect
Sheets("Main").Activate
End Sub

Pasting Data without shifting Down rows
 

Note the substituted code where you were using insert.

Workbooks.Open FileToOpen
Set oWB = Workbooks.Open(FileToOpen)
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
postRow =
Worksheets(1).UsedRange.SpecialCells(xlCellTypeLas tCell).Row + 2
Cells(postRow, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
oWB.Close

Try this on a copy before installing on the functional workbook.


"D Zandveld" wrote:

Hi, below is the code for importing a file into 'Imported Data'

Problem arises when the file is imported, references to the file in another
Worksheet (even stored as $constants) shift downward. The imported file
currently has 70 records, so if a new file is imported, the references shift
each time by 70 rows, therefore screwing up the formula results.

How can I clear the sheet, and paste the data other than using the
Selection.Insert Shift:=xlDown function?

Sub Import_Data()
' Import_Data Macro recorded 14/02/2007 by (me)
' This macro imports the data from the selected file output from the database
' This is limited to *.xls (default) or *.csv file formats

Dim oWB

Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
'ActiveWorksheet.Unprotect
Selection.CurrentRegion.Clear


With Application.FileDialog(msoFileDialogOpen)
FileToOpen = Application _
.GetOpenFilename("Comma Separated Values (*.csv),*.csv,Microsoft
Excel (*.xls),*.xls,All Files (*.*),*.*")

Workbooks.Open FileToOpen
Set oWB = Workbooks.Open(FileToOpen)
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Workbooks("SOiEM Genie v1.xls").Activate
Sheets("Imported Data").Activate
Range("A1").Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
oWB.Close

End With
'ActiveWorksheet.Protect
Sheets("Main").Activate
End Sub