Cutting & pasting from one workbook to another
 

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.

Cutting & pasting from one workbook to another
 

You mean, some code like this?

This needs to go into the Placements workbook's Workbook_Open event
processor. In Excel 2003 and earlier, easy way to get there is to
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu and choose [View Code] from the list presented to you.
Once there, cut and paste the code below into the module. Make any changes
to the Const values that I set up for you so that workbook names, worksheet
names and starting data rows and "most used" column are all identified
properly for your setup.

The only "catch" to this is that both of the workbooks must be in the same
directory (same folder).


Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'PlacementsSheet in this workbook to
'DumpSheet in the Dump workbook,
'deleting the row's data in the process
'in this workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row
If lastPlacementRow < firstPlacementDataRow Then
'no data to be transferred, just quit
Set placementWS = Nothing ' cleanup
Exit Sub
End If
'we do have work to do, continue on
'
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Offset(1, 0).Row ' never less than 2
If nextDumpRow < firstDumpRow Then
'safety net "just in case"
nextDumpRow = firstDumpRow
End If
'copy the rows
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Copy
'paste into dump.xls
dumpWS.Range("A" & nextDumpRow).PasteSpecial xlPasteAll
'delete the rows in this workbook
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Delete
'done, close up and go home
dumpWB.Close True
Application.DisplayAlerts = True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub


"Janelle S" wrote:

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.

Cutting & pasting from one workbook to another
 

Thank you JLatham. This works almost perfectly except that the data is cut
from Placements and pasted into Dump. I was after cutting from Dump and
pasting into Placements. I still want this to happen when opening Placements.
I tried reversing the code ie. replacing placements with dump and vice versa
but it didn't do anything. Any suggestions?


"JLatham" wrote:

You mean, some code like this?

This needs to go into the Placements workbook's Workbook_Open event
processor. In Excel 2003 and earlier, easy way to get there is to
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu and choose [View Code] from the list presented to you.
Once there, cut and paste the code below into the module. Make any changes
to the Const values that I set up for you so that workbook names, worksheet
names and starting data rows and "most used" column are all identified
properly for your setup.

The only "catch" to this is that both of the workbooks must be in the same
directory (same folder).


Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'PlacementsSheet in this workbook to
'DumpSheet in the Dump workbook,
'deleting the row's data in the process
'in this workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row
If lastPlacementRow < firstPlacementDataRow Then
'no data to be transferred, just quit
Set placementWS = Nothing ' cleanup
Exit Sub
End If
'we do have work to do, continue on
'
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Offset(1, 0).Row ' never less than 2
If nextDumpRow < firstDumpRow Then
'safety net "just in case"
nextDumpRow = firstDumpRow
End If
'copy the rows
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Copy
'paste into dump.xls
dumpWS.Range("A" & nextDumpRow).PasteSpecial xlPasteAll
'delete the rows in this workbook
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Delete
'done, close up and go home
dumpWB.Close True
Application.DisplayAlerts = True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub


"Janelle S" wrote:

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.

Cutting & pasting from one workbook to another
 

It's late (almost midnight). I'll revise the flow of data tomorrow and post
the code for you.

"Janelle S" wrote:

Thank you JLatham. This works almost perfectly except that the data is cut
from Placements and pasted into Dump. I was after cutting from Dump and
pasting into Placements. I still want this to happen when opening Placements.
I tried reversing the code ie. replacing placements with dump and vice versa
but it didn't do anything. Any suggestions?


"JLatham" wrote:

You mean, some code like this?

This needs to go into the Placements workbook's Workbook_Open event
processor. In Excel 2003 and earlier, easy way to get there is to
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu and choose [View Code] from the list presented to you.
Once there, cut and paste the code below into the module. Make any changes
to the Const values that I set up for you so that workbook names, worksheet
names and starting data rows and "most used" column are all identified
properly for your setup.

The only "catch" to this is that both of the workbooks must be in the same
directory (same folder).


Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'PlacementsSheet in this workbook to
'DumpSheet in the Dump workbook,
'deleting the row's data in the process
'in this workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row
If lastPlacementRow < firstPlacementDataRow Then
'no data to be transferred, just quit
Set placementWS = Nothing ' cleanup
Exit Sub
End If
'we do have work to do, continue on
'
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Offset(1, 0).Row ' never less than 2
If nextDumpRow < firstDumpRow Then
'safety net "just in case"
nextDumpRow = firstDumpRow
End If
'copy the rows
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Copy
'paste into dump.xls
dumpWS.Range("A" & nextDumpRow).PasteSpecial xlPasteAll
'delete the rows in this workbook
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Delete
'done, close up and go home
dumpWB.Close True
Application.DisplayAlerts = True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub


"Janelle S" wrote:

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.

Cutting & pasting from one workbook to another
 

See if this revision to the code doesn't move data in the direction you
really want it to go.
Just delete the previous code and replace it with this.

Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'DumpSheet in that workbook to
'PlacementsSheet in this workbook,
'deleting the row's data in the process
'in the DUMP workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'reinstate displaying regular Excel alerts
Application.DisplayAlerts = True
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Row ' never less than 1
If nextDumpRow < firstDumpRow Then
'nothing to copy, just exit
'done, close up shop and go home
GoTo CleanupAndExit
End If
'copy the rows
dumpWS.Rows(firstDumpRow & ":" & _
nextDumpRow).Copy
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row + 1 ' next empty row actually
If lastPlacementRow < firstPlacementDataRow Then
'safety net
lastPlacementRow = firstPlacementDataRow
End If
'paste into placements.xls
placementWS.Range("A" & lastPlacementRow). _
PasteSpecial xlPasteAll
'delete the rows in the dump.xls workbook
dumpWS.Rows(firstDumpRow & ":" & _
nextDumpRow).Delete
CleanupAndExit:
dumpWB.Close True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub



"Janelle S" wrote:

Thank you JLatham. This works almost perfectly except that the data is cut
from Placements and pasted into Dump. I was after cutting from Dump and
pasting into Placements. I still want this to happen when opening Placements.
I tried reversing the code ie. replacing placements with dump and vice versa
but it didn't do anything. Any suggestions?


"JLatham" wrote:

You mean, some code like this?

This needs to go into the Placements workbook's Workbook_Open event
processor. In Excel 2003 and earlier, easy way to get there is to
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu and choose [View Code] from the list presented to you.
Once there, cut and paste the code below into the module. Make any changes
to the Const values that I set up for you so that workbook names, worksheet
names and starting data rows and "most used" column are all identified
properly for your setup.

The only "catch" to this is that both of the workbooks must be in the same
directory (same folder).


Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'PlacementsSheet in this workbook to
'DumpSheet in the Dump workbook,
'deleting the row's data in the process
'in this workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row
If lastPlacementRow < firstPlacementDataRow Then
'no data to be transferred, just quit
Set placementWS = Nothing ' cleanup
Exit Sub
End If
'we do have work to do, continue on
'
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Offset(1, 0).Row ' never less than 2
If nextDumpRow < firstDumpRow Then
'safety net "just in case"
nextDumpRow = firstDumpRow
End If
'copy the rows
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Copy
'paste into dump.xls
dumpWS.Range("A" & nextDumpRow).PasteSpecial xlPasteAll
'delete the rows in this workbook
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Delete
'done, close up and go home
dumpWB.Close True
Application.DisplayAlerts = True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub


"Janelle S" wrote:

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.

Cutting & pasting from one workbook to another
 

Absolutely perfect. Thank you so much - you have made me one very happy girl.
And I love the way you put in all the explanations - it helps me to
understand what does what which will be very helpful in other exercises.

"JLatham" wrote:

See if this revision to the code doesn't move data in the direction you
really want it to go.
Just delete the previous code and replace it with this.

Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'DumpSheet in that workbook to
'PlacementsSheet in this workbook,
'deleting the row's data in the process
'in the DUMP workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'reinstate displaying regular Excel alerts
Application.DisplayAlerts = True
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Row ' never less than 1
If nextDumpRow < firstDumpRow Then
'nothing to copy, just exit
'done, close up shop and go home
GoTo CleanupAndExit
End If
'copy the rows
dumpWS.Rows(firstDumpRow & ":" & _
nextDumpRow).Copy
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row + 1 ' next empty row actually
If lastPlacementRow < firstPlacementDataRow Then
'safety net
lastPlacementRow = firstPlacementDataRow
End If
'paste into placements.xls
placementWS.Range("A" & lastPlacementRow). _
PasteSpecial xlPasteAll
'delete the rows in the dump.xls workbook
dumpWS.Rows(firstDumpRow & ":" & _
nextDumpRow).Delete
CleanupAndExit:
dumpWB.Close True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub



"Janelle S" wrote:

Thank you JLatham. This works almost perfectly except that the data is cut
from Placements and pasted into Dump. I was after cutting from Dump and
pasting into Placements. I still want this to happen when opening Placements.
I tried reversing the code ie. replacing placements with dump and vice versa
but it didn't do anything. Any suggestions?


"JLatham" wrote:

You mean, some code like this?

This needs to go into the Placements workbook's Workbook_Open event
processor. In Excel 2003 and earlier, easy way to get there is to
right-click on the Excel icon immediately to the left of the word "File" in
the main Excel menu and choose [View Code] from the list presented to you.
Once there, cut and paste the code below into the module. Make any changes
to the Const values that I set up for you so that workbook names, worksheet
names and starting data rows and "most used" column are all identified
properly for your setup.

The only "catch" to this is that both of the workbooks must be in the same
directory (same folder).


Private Sub Workbook_Open()
'when this workbook is opened, it will look for
'the other workbook (Dump) in the same folder
'and if it finds it there, it will open it
'also, then copy all rows from
'PlacementsSheet in this workbook to
'DumpSheet in the Dump workbook,
'deleting the row's data in the process
'in this workbook, and when finished
'will close the Dump.xls file
'
'you may change these Const values to
'match the file names, worksheet names
'and starting row for data in the books
'
Const dumpWBName = "dump.xls"
Const dumpWSName = "DumpSheet"
Const firstDumpRow = 2

Const placementSheetName = "PlacementsSheet"
Const firstPlacementDataRow = 2
'this next one should be a column
'on the PlacementsSheet that has
'data from firstPlacementDataRow all
'the way down to the last entry on the sheet
Const alwaysUsedColumn = "A"

Dim dumpWB As Workbook
Dim dumpWS As Worksheet
Dim nextDumpRow As Long

Dim placementWS As Worksheet
Dim lastPlacementRow As Long

Dim filePath As String

filePath = ThisWorkbook.Path & Application.PathSeparator _
& dumpWBName
If Dir$(filePath) = "" Then
'dump.xls not found, just quit
Exit Sub
End If
'see if we have anything to be copied
Set placementWS = ThisWorkbook.Worksheets(placementSheetName)
lastPlacementRow = placementWS.Range(alwaysUsedColumn & _
Rows.Count).End(xlUp).Row
If lastPlacementRow < firstPlacementDataRow Then
'no data to be transferred, just quit
Set placementWS = Nothing ' cleanup
Exit Sub
End If
'we do have work to do, continue on
'
'hide the operations from view
Application.ScreenUpdating = False
'no nags from the system
Application.DisplayAlerts = False
'open dump.xls without updating links
'dump.xls becomes the active workbook
Workbooks.Open filePath, 0
'back to this workbook
'set reference to dump.xls
Set dumpWB = Workbooks(dumpWBName)
'reference to the dump sheet
Set dumpWS = dumpWB.Worksheets(dumpWSName)
'find next available row on dump sheet
nextDumpRow = dumpWS.Range(alwaysUsedColumn & Rows.Count). _
End(xlUp).Offset(1, 0).Row ' never less than 2
If nextDumpRow < firstDumpRow Then
'safety net "just in case"
nextDumpRow = firstDumpRow
End If
'copy the rows
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Copy
'paste into dump.xls
dumpWS.Range("A" & nextDumpRow).PasteSpecial xlPasteAll
'delete the rows in this workbook
placementWS.Rows(firstPlacementDataRow & ":" & _
lastPlacementRow).Delete
'done, close up and go home
dumpWB.Close True
Application.DisplayAlerts = True
Set dumpWS = Nothing
Set dumpWB = Nothing
Set placementWS = Nothing
End Sub


"Janelle S" wrote:

Hi - I am after a VBA code that opening one workbook (placements) will
automatically open another workbook (dump) and then cut and paste rows from
the (dump) workbook into the (placements) workbook. Rows in (dump) will then
be deleted leaving it without data. The (dump) workbook will then save and
close and user will be returned to the (placements) workbook.
Hoping you can help. Thanks.