I've put this together to assist backup of multiple workbook data to defined locations. There is no error checking to ensure that files etc. exist, and it is not comprehensive with PasteSpecial options (easily added)
Feedback welcome.
Regards
MD
I've put this together to assist backup of multiple workbook data to defined locations. There is no error checking to ensure that files etc. exist, and it is not comprehensive with PasteSpecial options (easily added)
Feedback welcome.
Regards
MD
Last edited by mdmackillop; 07-20-2008 at 08:22 AM. Reason: Attachment updated as Post #3
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
the utility does not copy data from the source to target as required.
testing files enclosed
Thank you.
Last edited by WINFS; 07-19-2008 at 06:49 PM.
Thanks for the feedback.
You changed the name of the workbook which was used in the code. Replace DoCopy with this section of code which will allow for this.
[VBA]
Sub DoCopy()
Dim CopyRange As Range
Dim Cel As Range, Tgt As Range
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim WB As Workbook
On Error GoTo Exits
Application.EnableEvents = False
Application.ScreenUpdating = False
Set WB = ThisWorkbook
Set CopyRange = Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp))
For Each Cel In CopyRange
'Set reference ranges
Set Tgt = Cel.Offset(, 5)
Set SourceRange = Cel.Offset(, 3)
Set TargetRange = Tgt.Offset(, 3)
Set CopyOptions = Cel.Offset(, 9)
Set PasteSpecialOption = Cel.Offset(, 9).Resize(, 4)
'Set Source File & Sheet
OpenBook Cel, Cel.Offset(, 1).Text
Set wsSource = wbSource.Sheets(Cel.Offset(, 2).Text)
'Set Target File & Sheet
OpenBook Tgt, Tgt.Offset(, 1).Text
Set wsTarget = wbTarget.Sheets(Tgt.Offset(, 2).Text)
WB.Activate
CopyData wsSource, wsTarget
'Close books if not required for next process
If StayOpenSource = False Then
wbSource.Close False
Set wbSource = Nothing
End If
If StayOpenTarget = False Then
wbTarget.Close True
Set wbTarget = Nothing
End If
Next Cel
Exits:
If Not wbSource Is Nothing Then wbSource.Close False
If Not wbTarget Is Nothing Then wbTarget.Close True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Update the codes. It copies the data in first source file to the target file and worksheet, but did not copy anythings from the second source file to the target file. test files enclosed.
Your second workbook has all sheets selected. The copy command copies a range from all twelve sheets and therefor requires a minumum 12 sheet workbook to paste into. Add the extra sheets to Test and the code runs.
It does not seem good practice to save a workbook in this state. I'll need to add a check to detect this and cancel the copy to avoid unwanted changes.
Thanks for this. I would never have thought of this situation.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi,
Your file is really great! And it really help me to save lots of time.
By the way, is there any way which you can modify to save the file into different folders? Using the same concept?
Thanks.
I'm not entirely clear what you are after. If you wany to save copies of the whole workbook, you can use SaveCopyAs.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Hi, I tried using your code to paste in the sheets from 2 different workbooks into 2 different tabs in a same workbook.
Apparently, the macro can only paste into the first tab and have problem pasting into my second tab.
Illustration:
Workbook A - Summary Tab
Workbook B - Summary Tab
I need to paste the summary tab from workbook A and B into a main workbook C. But i will predefine 2 tabs within workbook C.
Workbook A - Summary tab ==> Paste into Workbook C - Wkbk A tab
Workbook B - Summary tab ==> Paste into Workbook C - Wkbk B tab
Can help urgently??
Hi, the macro managed to work. However, if i do not wish to update the workbooks, how should i amend the script?
Thanks for your help!!
Hi, there's another problem.. i can't seem to paste value the files...
Can help?