Macro Help Archive Data

[Dastnai]

Hi Everyone,

I am attempting to copy data from one sheet to another. My copied data is A3:R52. However, i want it to paste the data at the next empty cell given there are not values everytime in A3:R52.


Here is my current code:

Range("A3:R52").Select
Selection.Copy
Sheets("Archived Data").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select
End Sub


Thanks for your help,

Dastnai

 

[mrshl9898]

Hi,

Try:

Dim sht As Worksheet
Dim LastRow As Long


Set sht = Sheets("Archived Data")


LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Range("A3:R52").Copy 
Sheets("Archived Data").Range("A" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select


End Sub

 

[Dastnai]

Hi,

Try:

Dim sht As Worksheet
Dim LastRow As Long


Set sht = Sheets("Archived Data")


LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Range("A3:R52").Copy 
Sheets("Archived Data").Range("A" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select


End Sub

I am getting an error here:

Dim sht As Worksheet
Dim LastRow As Long




Set sht = Sheets("Archived Data")




LastRow = sht.Cells(sht.rows.Count, "A").End(xlUp).Row




Range("A3:R52").Copy
Sheets("Archived Data").Range("A" & LastRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select




End Sub

 

[mrshl9898]

apologies

Sub paste()


Dim sht As Worksheet
Dim LastRow As Long




Set sht = Sheets("Archived Data")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row




Range("A3:R52").Copy
sht.Select
sht.Range("A" & LastRow).Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select




End Sub

 

[Dastnai]

apologies

Sub paste()


Dim sht As Worksheet
Dim LastRow As Long




Set sht = Sheets("Archived Data")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row




Range("A3:R52").Copy
sht.Select
sht.Range("A" & LastRow).Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False




Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select




End Sub

Unfortunately, this one is pasted the entire range. A bit stumped on this.

 

[mrshl9898]

Oh, I thought that was the goal.

Can you elaborate on

However, i want it to paste the data at the next empty cell given there are not values everytime

I took it to mean there were gaps in the sheet being pasted to and A1 with xldown wasn't finding the last row.

Some sample data would be helpful perhaps.

 

[Dastnai]

Oh, I thought that was the goal.

Can you elaborate on

I took it to mean there were gaps in the sheet being pasted to and A1 with xldown wasn't finding the last row.

Some sample data would be helpful perhaps.

Thanks for your help Mrshl.

On my INQ Dashboard tab I am trying to copy range A3:R52. These cells all contain formulas. Some the output is a value or blank. My goal is to copy the range, paste as values into a new worksheet and to only paste the cells that are not blank. For instance, if I run my current code it copies and pastes the entire range so if I rerun the code it will now go to paste the next row of data 50 spots from the previous data inputted.

 

[mrshl9898]

Is it whole blank rows you are looking to exclude?

Sub paste()


Dim sht As Worksheet
Dim LastRow As Long


Set sht = Sheets("Archived Data")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Range("A3:R52").Copy
sht.Select
sht.Range("A" & LastRow).Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete




Sheets("INQ Dashboard").Select
Application.CutCopyMode = False
Range("S3").Select


End Sub

If it's just blank cells, what do you want to happen to the data around the cell?

 

[mrshl9898]

if I rerun the code it will now go to paste the next row of data 50 spots from the previous data inputted.

It shouldn't be. Yours and mine both look for the last row containing data.

You could replace

[COLOR=#333333]Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/COLOR]

with

Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

if this is still happening, shouyld remove all blank rows from the destination sheet.

 

[Dastnai]

It shouldn't be. Yours and mine both look for the last row containing data.

You could replace

[COLOR=#333333]Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/COLOR]

with

Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

if this is still happening, shouyld remove all blank rows from the destination sheet.

After I paste the data I am looking for it to allow me to paste again for the next row(s) of data. My current code there is a gap between the paste I was thinking a workaround was to delete blanks rows after. I am still getting an error. Would it be possible for me to send you a sample file?