Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel VBA
I have a project. I will give the description of the project at the end. For now, suffice to say, I am running into problems with my code.
The macro shortcut keys are CTRL+Shift+N. The current problem is that the ActiveSheet.Paste command is not working. I did some research and read that this happens and that you might try re-starting the workbook. I have done that. The problem starts with the first paste command.
 
One other aspect of the project that I am unsure of is a loop. I want this to loop based upon finding any other records in the East Trial Balance worksheet that have NEW_ACCT in column F. I am not sure how to write that loop.
 
Now for the description of the project...
 
I have a workbook. There are six worksheets I want to use for this project. The first is the reference worksheet called East Trial Balance. This sheet gets populated in columns A thru D from a database. In column F is a formula that looks for a record in worksheet Tax Basis BS East for a value in column B that equals the value of column A in the East Trial Balance. If there is none, the word NEW_ACCT appears.
Using that, I want to loop through any and all records in the East Trial Balance that have NEW_ACCT in column F and I want to add the values from columns A and C to the Tax Basis BS East worksheet.
Here is the tricky part...
The record to be added must go into the section whose name matches the value of column D on the East Trial Balance. It must find the matching section in the Tax Basis BS East by searching column A. It must then move the cursor up one row and then insert a new row. This way I do not loose my formulas. Then in column A of the Tax Basis BS East, add the value from column C of the East Trial Balance and in column B of the Tax Basis BS East, add the value from column A of the East Trial Balance.
Then I need to copy the formulas that exist in columns C through N of the row above this new record to the same columns for the new record.
Not done yet...
before proceeding to the next record that is new, I want to repeat the append portion for worksheets Tax Basis BS Other, Tax Basis BS SL, Tax Basis BS VIE, and Tax Basis BS West.
 
Here is the code I have developed to date...
 
Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    Dim strSection As String
    Dim intRowA As String
    Dim intRowB As String
    
    ' Go to Trial Balance and search for a New Account
    Sheets("East Trial Balance").Select
    Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ' Move from column F to column D to get the name of the Section for the New Account
    ActiveCell.Offset(0, -2).Select
    strSection = ActiveCell.Value
    
    ' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' Move from column D to column A to get the Account Number
    Sheets("East Trial Balance").Select
    ActiveCell.Offset(0, -3).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ' Move to the column B and paste the copied Account Number
    Sheets("Tax Basis BS-SL").Select
    ActiveCell.Offset(0, 1).Select
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowB).Select
    ActiveSheet.Paste
    
    ' Go back to the Trial Balance and get a copy of the Account Name from column C
    Sheets("East Trial Balance").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 2).Select
    Selection.Copy
    
    ' Return to the Tax Basis BS and paste the Account Name
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-2, 0).Select
    intRowA = "A" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-West").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-West").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-Other").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-Other").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-VIE").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-VIE").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
 
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-East").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-East").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
 
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
End Sub
 
Again the code fails at the first ActiveSheet.Paste.
Second I need help writing the loop that will allow this code to repeat for any record in the East Trail Balance having NEW_ACCT in column F.
Posted 17-Dec-12 8:34am
Edited 17-Dec-12 8:48am
v2
Comments
rawilken at 17-Dec-12 18:24pm
   
Still have Paste method failed errors.
My code is now...
Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
' then copy the formulas from the line above into the new line
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim strFound As String
Dim strSection As String
Dim intWorkSht As Integer

strFound = Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
 
Do While strFound <> vbNullString
' Move from column F to column D to get the name of the Section for the New Account
ActiveCell.Offset(0, -2).Select
strSection = ActiveCell.Value

' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Move from column D to column A to get the Account Number
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, -3).Select
Selection.Copy

' Move to the column B and paste the copied Account Number
Sheets("Tax Basis BS-SL").Select
ActiveCell.Offset(0, 1).Select
Range("B" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

' Go back to the Trial Balance and get a copy of the Account Name from column C
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, 2).Select
Selection.Copy

' Return to the Tax Basis BS and paste the Account Name
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Select
Range("A" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues

' Go to column c at the row above and copy all the formulas in that row to the added row
ActiveCell.Offset(-1, 2).Select
Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).PasteSpecial Paste:=xlPasteValues

intWorkSht = 1
 
Do While intWorkSht < 5
' Go to the next Tax Basis BS worksheet and append a new row in the correct section
If intWorkSht = 1 Then
Sheets("Tax Basis BS-West").Select
ElseIf intWorkSht = 2 Then
Sheets("Tax Basis BS-Other").Select
ElseIf intWorkSht = 3 Then
Sheets("Tax Basis BS-VIE").Select
ElseIf intWorkSht = 4 Then
Sheets("Tax Basis BS-East").Select
End If
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Return to the Tax Basis BS SL and copy the A

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

There could be several reasons for the behaviour. One thing you could try is to use PasteSpecial instead of Paste. Since you're pasting a range, it could work better for you. Refer to Range.PasteSpecial Method[^]
 
Another thing is that if the selection for target range is differently sized than the source range, it would cause problems.
 
Also try using the debugger to see the ranges and that the copying is done properly.
  Permalink  
Comments
rawilken at 17-Dec-12 15:37pm
   
Newly revised code. Problem still occurs. Paste fails! Need help adding Do loop to repeat for each entry of NEW_ACCT in column F of East Trail Balance.
 
Code...
 
Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
'
' Keyboard Shortcut: Ctrl+Shift+N
'
Dim strSection As String
Dim intRowA As String
Dim intRowB As String
Dim intWorkSht As Integer

' Go to Trial Balance and search for a New Account
Sheets("East Trial Balance").Select
Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

' Move from column F to column D to get the name of the Section for the New Account
ActiveCell.Offset(0, -2).Select
strSection = ActiveCell.Value

' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
intRowA = ActiveCell.Row
intRowB = ActiveCell.Row
Range(intRowA & ":" & intRowB).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

' Move from column D to column A to get the Account Number
Sheets("East Trial Balance").Select
ActiveCell.Offset(0, -3).Select
Application.CutCopyMode = False
Selection.Copy

' Move to the column B and paste the copied Account Number
Sheets("Tax Basis BS-SL").Select
ActiveCell.Offset(0, 1).Select
intRowB = "B" & ActiveCell.Row
Application.CutCopyMode = False
Range(intRowB).PasteSpecial Paste:=xlPasteValues

' Go back to the Trial Balance and get a copy of the Account Name from column C
Sheets("East Trial Balance").Select
Application.CutCopyMode = False
ActiveCell.Offset(0, 2).Select
Selection.Copy

' Return to the Tax Basis BS and paste the Account Name
Sheets("Tax Basis BS-SL").Select
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-2, 0).Select
intRowA = "A" & ActiveCell.Row
Application.CutCopyMode = False
Range(intRowA).PasteSpecial Paste:=xlPasteValues

' Go to column c at the row above and copy all the formulas in that row to the added row
ActiveCell.Offset(-1, 2).Select
intRowA = "C" & ActiveCell.Row
Range(intRowA).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveCell.Offset(1, 0).Select
intRowA = "C" & ActiveCell.Row
Range(intRowA).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
 
intWorkSht = 1

Do While intWorkSht < 5
' Go to the next Tax Basis BS worksheet and append a new row in the correct section
If intWorkSht = 1 Then
Sheets("Tax Basis BS-West").Select
ElseIf intWorkSht = 2 Then
Sheets("Tax Basis BS-Other").Select
ElseIf intWorkSht = 3 Then
Sheets("Tax Basis BS-VIE").Select
ElseIf intWorkSht = 4 Then
Sheets("Tax Basis BS-East").Select
End If
Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(-1, 0).Select
intRowA = ActiveCell.Row
intRowB = ActiveCell.Row
Range(intRowA &
Nelek at 17-Dec-12 18:21pm
   
Please don't add code blocks of this size in the comments. It is very difficult to read. You can use the "improve question" widget to change your previous post, use it to add/edit what you need

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 371
1 Sergey Alexandrovich Kryukov 286
2 RyanDev 75
3 PhilLenoir 70
4 Pheonyx 50
0 Sergey Alexandrovich Kryukov 6,676
1 OriginalGriff 6,056
2 CPallini 2,473
3 Richard MacCutchan 1,697
4 Abhinav S 1,560


Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100