CODEWITHSUNDEEP
excelvbacopy-pastecopyingpasting
richienrg
richienrg

Reputation: 3

Pasting Data in Next Blank From "A4"

I have 4 tabs..

Year 1, Year 2, Year 3, Total Spend.

Year 1 pastes in the Total Spend tab like so:

https://i.sstatic.net/SegGE.png.

Now, I need the data from Year 2 and Year 3 to copy from Row 4 down and pasted into the next row blank in Total Spend.

This is the code I am using to label the Year 2 and 3 range and to attempt to find the first blank cell in column A to paste the data.

I can't figure out why this isn't working. It doesn't paste anything at any location.

    Dim Year2 As Range
Dim Year3 As Range
Dim Location As Range

Set Year2 = Sheets("Year 2 Data").Range("4:4").End(xlDown)
Set Year3 = Sheets("Year 3 Data").Range("4:4").End(xlDown)
Set Location = Sheets("Total Spend").Cells(Rows.Count, 1).End(xlUp)
If Len(Location.Value) > 0 Then Set Location = Location.Offset(1)


 ' This paste is manual so Row 2 stays blank - this is needed in formatting later on. 

    Sheets("Year 1 Data").Select
    Cells.Select
    Selection.Copy
    Sheets("Total Spend").Select
    Cells.Select
    ActiveSheet.Paste

Year2.Copy Location

Year3.Copy Location

Upvotes: 0

Views: 82

Answers (4)

Knowledge Game
Knowledge Game

Reputation: 51

Ohh! use below code to solve that error. i have removed "+88" from rows.count to solve that error.

Set Location = Sheets("Total Spend").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Upvotes: 0

Knowledge Game
Knowledge Game

Reputation: 51

Ok, so as per your logic, I think below code will help. It will select the data from sheet, and paste data in output sheet using last row of output sheet.

n A to paste the data.

I can't figure out why this isn't working. It doesn't paste anything at any location.

Dim Year2 As Range

Dim Year3 As Range Dim Location As Range

Set Year2 = Range(Sheets("Year 2 Data").Range("4:4"), Sheets("Year 2 Data").Range("4:4").End(xlDown))

Set Year3 = Range(Sheets("Year 3 Data").Range("4:4"), Sheets("Year 3 Data").Range("4:4").End(xlDown)) Set Location = Sheets("Total Spend").Cells(Rows.Count+88, 1).End(xlUp).offset(1, 0)

If Len(Location.Value) > 0 Then Set Location = Location.Offset(1)

' This paste is manual so Row 2 stays blank - this is needed in formatting later on.

Sheets("Year 1 Data").Select
Cells.Select
Selection.Copy
Sheets("Total Spend").Select
Cells.Select
ActiveSheet.Paste

Sheets("Total Spend").Cells(Rows.Count+88, 1).End(xlUp).offset(1, 0).select

Year2.Copy Location

Sheets("Total Spend").Cells(Rows.Count+88, 1).End(xlUp).offset(1, 0).select

Year3.Copy Location

Upvotes: 0

richienrg
richienrg

Reputation: 3

After taking a look at the comments, would something like this be the answer? (I cannot test it yet as I’ve left work.)

Dim Year2 As Range
Dim Year3 As Range
Dim Location As Range

Set Year2 = Range(Sheets("Year 2 Data").Range("4:4"), Sheets("Year 2 Data").Range("4:4").End(xlDown))

Set Year3 = Range(Sheets("Year 3 Data").Range("4:4"), Sheets("Year 3 Data").Range("4:4").End(xlDown))

Set Location = Sheets("Total Spend").Cells(Rows.Count, 3).End(xlUp)

‘I have changed the above to begin at Row 3, skipping the blank Row 2 so theoretically it would check using the IF from Row 3 down.

If Len(Location.Value) > 0 Then Set Location = Location.Offset(1)

' This paste is manual so Row 2 stays blank - this is needed in formatting later on.

Sheets("Year 1 Data").Select
Cells.Select
Selection.Copy
Sheets("Total Spend").Select
Cells.Select
ActiveSheet.Paste

Year2.Copy Location

Year3.Copy Location

Upvotes: 0

Knowledge Game
Knowledge Game

Reputation: 51

use this

'    Range(Sheets("Year 2 Data").Range("4:4"), Sheets("Year 2 Data").Range("4:4").End(xlDown))

instead of

Sheets("Year 2 Data").Range("4:4").End(xlDown)

same thing will be apply for Sheets("Year 3 Data")

Upvotes: 0

Related Questions