VBA Copy Paste Value From Certain Word

[luke1438]

I want to copy paste value to sheet 2 (a different sheet then where this table sits) everything in columns “R” through “U” from the word “Income” in Column “T” down to where there is a 0 (Zero) in Column “T”. Any suggestions would be helpful. My VBA experience is using the macro recorder and I cannot figure out how to use that because this data is always changing.

Thanks
Luke

 

[LMacD]

Questions? Is this in a table or just rows a columns in a spreadsheet?
If it is row and columns in a spreadsheet, are they always in the same position? Does the data start on row 1.
If this is a table with headers it would be a simple sub.
If not them making some assumptions about its position, like it always started at R1 would make the sub a little less complex.

 

[luke1438]

Its just rows and columns in a spreadsheet. They never change in columns but the data does change so the rows may be longer or shorter. It starts on row 2, R2.

Regards
Luke

 

[bebo021999]

Is this indeed from one accounting system?
then do you want to copy the P/L with accounts starting from 4, 5, and 6?
You have 2 options: one is using array with For...Next loop, and other with Advance Filter

 

[LMacD]

This will do what you want... BUT there is too many assumptions that must be true for this to work.
IMO it would be easier and safer to just do a manual copy and paste.

Option Explicit

'*************************************************************************
'* Assumptions                                                           *
'* The start of data is always Col R Row 2.                              *
'* Col T always contains Income and it is always in the same order       *
'* The data is on worksheet "Sheet1"                                     *
'* Worksheet "Sheet2" is in the same workbook                            *
'* There is always "Income" and a '0" in row R                           *
'*************************************************************************

Public Sub CopyPasteFinancials()
    Dim LastRow As Integer
    Dim StartRow As Integer
    Dim CopyFrom As Range
    
    'The data is on Sheet1
    Worksheets("Sheet1").Activate
    
    'The word "Income" is in column 20 The start row is the first row with Income"
    StartRow = Columns(20).Find(What:="Income", LookAt:=xlWhole, MatchCase:=False).Row
    
    'There is "0" in Column 20
    LastRow = Columns(20).Find(What:="0", LookAt:=xlWhole, MatchCase:=False).Row

    'Copy down to the row before the 0
    LastRow = LastRow - 1
    Set CopyFrom = Range(Cells(StartRow, 18), Cells(LastRow, 21))
    CopyFrom.Copy
    
    'Paste this into Sheet2 sstart with cell A1
    Sheets("Sheet2").Select
    Cells(1, 1).PasteSpecial xlPasteAll
End Sub

 

[luke1438]

Yes, it is from one accounting system, QuickBooks. Yes, all P/L accounts starting with 4, 5 and 6. Not sure which is better, Next loop or Advanced Filter. I just don't want it to miss anything.

Thanks
Luke

 

[luke1438]

Hello LMacD,

I am understanding your code and see how it can work (itis very well notated), but what does "Option Explicit" mean? Also, the word "Public" in front of "Sub CopyPasteFinancials()" what does that do? Your list of assumptions is correct and makes sense for the rest of the code. I changed the sheet names and tried to run it but it hangs up on the "Option Explicit".

Regards
Tim

 

[Peter_SSs]

it hangs up on the "Option Explicit".

By "hangs" do you mean that you get an error message that says "Compile error" etc?
If so, you should have just one "Option Explicit" and it should be at the top of the module.

what does "Option Explicit" mean?

It ensures that you specifically declare (usually with a Dim statement) all the variables that are used. It is a good idea.
More info here

Also, the word "Public" in front of "Sub CopyPasteFinancials()" what does that do?

Explained here

 

[LMacD]

Hello LMacD,

I am understanding your code and see how it can work (itis very well notated), but what does "Option Explicit" mean? Also, the word "Public" in front of "Sub CopyPasteFinancials()" what does that do? Your list of assumptions is correct and makes sense for the rest of the code. I changed the sheet names and tried to run it but it hangs up on the "Option Explicit".

Regards
Tim

I use Public Sub (instead of just sub) if I want to call this from another module or form. I use Private Sub if I want it to be only used by a module or form
How to you execute the code? Is it from a button on the spreadsheet or F5 from the module?
Peter explained the use of Option Explicit. It is not an executable; it is only of value if you are making changes to the code. You can delete it is you wish, and it will not affect the execution.

 

[luke1438]

Awesome, I took out the "Option Explicit" and the "Public Sub" and it works perfectly. Thank you all for your help; very much appreciated. Code is below with some minor tweaks:

Sub CopyPasteFinancials()
    Dim LastRow As Integer
    Dim StartRow As Integer
    Dim CopyFrom As Range
    
    'The data is on Sheet1
    Worksheets("Sheet 1").Activate
    
    'The word "Income" is in column 20 The start row is the first row with Income"
    StartRow = Columns(20).Find(What:="Income", LookAt:=xlWhole, MatchCase:=False).Row
    
    'There is "0" in Column 20
    LastRow = Columns(20).Find(What:="0", LookAt:=xlWhole, MatchCase:=False).Row

    'Copy down to the row before the 0
    LastRow = LastRow - 1
    Set CopyFrom = Range(Cells(StartRow, 18), Cells(LastRow, 21))
    CopyFrom.Copy
    
    'Paste this into Sheet2 sstart with cell A1
    Sheets("Sheet 2").Select
    Cells(1, 1).PasteSpecial xlPasteValues
End Sub