Campbell987
11-30-2013, 05:04 PM
Hi All!
I created a VBA macro to copy data from specific worksheets in a workbook to another workbook with the same worksheet names. I am having trouble looping through the workbook to copy data from each of the worksheets. I am not sure where I should place the "For" and "Next" statement. Also, I do not think the code I have for looping through the workbook is the correct code. There are 12 worksheets (i.e. Jan13, Feb13, Mar13, etc.). The worksheets names will be the same on both the source and target workbooks.
I am getting an error (I bold the statement below) where it states the "Object does not support this property or method".
Below is my VBA Code.
Any input would be very appreciated. Thanks in advance for your help.
- Ron -
Sub Copy_Data_Items()
' Copy items from Source Workbook to Target Workbook.
If MsgBox("Do You Want to Copy Data?", vbYesNo + vbDefaultButton2) = vbNo Then
End
End If
If MsgBox("Are you Sure?", vbYesNo + vbDefaultButton2) = vbNo Then
End
End If
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbSource As Workbook 'workbook from where the data is to copied
Dim strSourceFile As String
Dim strTargetFile As String
strSourceFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13old.xlsm"
strTargetFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13new.xlsm"
Dim i As Long
'Set to the current active workbook (the source book)
Set wbSource = ActiveWorkbook
'Open a Target workbook
Set wbTarget = Workbooks.Open(strTargetFile)
For i = 1 To 12
Sheets(i + 1).Select
'Clear existing values from target book
Range("C12:G35") = ClearContents
Range("C38:G52") = ClearContents
Range("B60:G109") = ClearContents
Range("B116:G215") = ClearContents
Range("D216:G216") = ClearContents
Range("I66:O85") = ClearContents
Range("L086:O86") = ClearContents
Range("I94:O108") = ClearContents
Range("L109:O109") = ClearContents
Range("I118:O126") = ClearContents
Range("L127:O127") = ClearContents
Range("I135:O144") = ClearContents
Range("I153:O160") = ClearContents
Range("L161:O161") = ClearContents
Range("I169:O178") = ClearContents
Range("I186:O215") = ClearContents
Range("L216:O216") = ClearContents
'Select cell C12 on the target book
wbTarget.Range("C12:G35").Select
'Activate the source book
wbSource.Activate
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("C12:G35").Copy
'Paste the data on the target book
wbTarget.Range("C12").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("C38:G52").Copy
'Paste the data on the target book
wbTarget.Range("C38").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("B60:G109").Copy
'Paste the data on the target book
wbTarget.Range("B60").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("B116:G216").Copy
'Paste the data on the target book
wbTarget.Range("B116").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I66:O86").Copy
'Paste the data on the target book
wbTarget.Range("I66").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I94:O109").Copy
'Paste the data on the target book
wbTarget.Range("I94").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I118:O127").Copy
'Paste the data on the target book
wbTarget.Range("I118").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I135:O144").Copy
'Paste the data on the target book
wbTarget.Range("I135").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I153:O161").Copy
'Paste the data on the target book
wbTarget.Range("I153").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I169:O178").Copy
'Paste the data on the target book
wbTarget.Range("I169").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I186:O216").Copy
'Paste the data on the target book
wbTarget.Range("I186").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Save the target book
wbTarget.Save
'Close the workbook
wbTarget.Close
'Activate the source book again
wbSource.Activate
'Clear memory
Set wbTarget = Nothing
Set wbSource = Nothing
Range("A1").Select
PctDone = i / 12
With UserForm3
.FrameProgress.Caption = VBA.Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
Next i
Unload UserForm3
End Sub
I created a VBA macro to copy data from specific worksheets in a workbook to another workbook with the same worksheet names. I am having trouble looping through the workbook to copy data from each of the worksheets. I am not sure where I should place the "For" and "Next" statement. Also, I do not think the code I have for looping through the workbook is the correct code. There are 12 worksheets (i.e. Jan13, Feb13, Mar13, etc.). The worksheets names will be the same on both the source and target workbooks.
I am getting an error (I bold the statement below) where it states the "Object does not support this property or method".
Below is my VBA Code.
Any input would be very appreciated. Thanks in advance for your help.
- Ron -
Sub Copy_Data_Items()
' Copy items from Source Workbook to Target Workbook.
If MsgBox("Do You Want to Copy Data?", vbYesNo + vbDefaultButton2) = vbNo Then
End
End If
If MsgBox("Are you Sure?", vbYesNo + vbDefaultButton2) = vbNo Then
End
End If
Dim wbTarget As Workbook 'workbook where the data is to be pasted
Dim wbSource As Workbook 'workbook from where the data is to copied
Dim strSourceFile As String
Dim strTargetFile As String
strSourceFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13old.xlsm"
strTargetFile = "C:\Users\Ronola\Documents\My Safe\My Budget\Finance13new.xlsm"
Dim i As Long
'Set to the current active workbook (the source book)
Set wbSource = ActiveWorkbook
'Open a Target workbook
Set wbTarget = Workbooks.Open(strTargetFile)
For i = 1 To 12
Sheets(i + 1).Select
'Clear existing values from target book
Range("C12:G35") = ClearContents
Range("C38:G52") = ClearContents
Range("B60:G109") = ClearContents
Range("B116:G215") = ClearContents
Range("D216:G216") = ClearContents
Range("I66:O85") = ClearContents
Range("L086:O86") = ClearContents
Range("I94:O108") = ClearContents
Range("L109:O109") = ClearContents
Range("I118:O126") = ClearContents
Range("L127:O127") = ClearContents
Range("I135:O144") = ClearContents
Range("I153:O160") = ClearContents
Range("L161:O161") = ClearContents
Range("I169:O178") = ClearContents
Range("I186:O215") = ClearContents
Range("L216:O216") = ClearContents
'Select cell C12 on the target book
wbTarget.Range("C12:G35").Select
'Activate the source book
wbSource.Activate
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("C12:G35").Copy
'Paste the data on the target book
wbTarget.Range("C12").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("C38:G52").Copy
'Paste the data on the target book
wbTarget.Range("C38").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("B60:G109").Copy
'Paste the data on the target book
wbTarget.Range("B60").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("B116:G216").Copy
'Paste the data on the target book
wbTarget.Range("B116").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I66:O86").Copy
'Paste the data on the target book
wbTarget.Range("I66").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I94:O109").Copy
'Paste the data on the target book
wbTarget.Range("I94").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I118:O127").Copy
'Paste the data on the target book
wbTarget.Range("I118").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I135:O144").Copy
'Paste the data on the target book
wbTarget.Range("I135").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I153:O161").Copy
'Paste the data on the target book
wbTarget.Range("I153").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I169:O178").Copy
'Paste the data on the target book
wbTarget.Range("I169").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Copy the range from source book
wbSource.Range("I186:O216").Copy
'Paste the data on the target book
wbTarget.Range("I186").PasteValues
'Clear anything on clipboard to maximize available memory
Application.CutCopyMode = False
'Save the target book
wbTarget.Save
'Close the workbook
wbTarget.Close
'Activate the source book again
wbSource.Activate
'Clear memory
Set wbTarget = Nothing
Set wbSource = Nothing
Range("A1").Select
PctDone = i / 12
With UserForm3
.FrameProgress.Caption = VBA.Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
' The DoEvents statement is responsible for the form updating
DoEvents
Next i
Unload UserForm3
End Sub