VBA Code Paste Next empty Cell other sheet

[guilli_003]

Hello,

I am trying to create a Macro to Paste a selected range (A1:F48) from on worksheet ("Sheet1") to another one ("Sheet5") in the same workbook.
The "Sheet5" is empty, and i'd like the macro (When run for the 1st time) to paste the copied range from "Sheet1" in the cell A1 of "Sheet5".
For the 2nd, 3rd, 4th... Xth time, i'd like it to paste the selected range at the last cell with something in it (in the column A of "Sheet5").

Here is the idea of the code that doesn't work because it's an empty worksheet and i cannot tell it to go paste in A1:

Sheets("Sheet1").Select
Range("A1").Select
Range("A1:F48").Select
Selection.Copy
Sheets("Sheet5").Select
Range("A1").Select
Selection.End(xlDown).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste

Thanks in advance for your help.

Kind Regards,
Guillaume.

 

[AlphaFrog]

Hi guys,

Not trying to Hijack this thread but pertains to EXACTLY what i need except i have data in Row 50 and when i run this code it pastes the data below that row on 51.

And that's offcourse because of that .END statement. is there a way to define the range to go and only search for empty cells from A1 to A49?

Thanx in advance

Dim lst As LongSheets("Sheet1").Range("A1:F48").Copy
With Sheets("Sheet5")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & lst).PasteSpecial xlPasteValues
End With

This finds the next empty row above A50, then pastes the 48 copied rows.

I don't know how you want to handle pasting part of the copied row block below A49? The code currently copies 48 rows. So if the next empty row is say row 8, it will paste from row 8 to row 56.

    [COLOR=darkblue]With[/COLOR] Sheets("Sheet5")
        [COLOR=darkblue]If[/COLOR] .Range("A49").Value <> "" [COLOR=darkblue]Then[/COLOR]
            MsgBox "Range A1:A49 is full. ", vbExclamation, "Copy\Paste Canceled"
        [COLOR=darkblue]Else[/COLOR]
            Sheets("Sheet1").Range("A1:F48").Copy
            .Range("A50").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

 

[deletedalien]

This finds the next empty row above A50, then pastes the 48 copied rows.

I don't know how you want to handle pasting part of the copied row block below A49? The code currently copies 48 rows. So if the next empty row is say row 8, it will paste from row 8 to row 56.

    [COLOR=darkblue]With[/COLOR] Sheets("Sheet5")
        [COLOR=darkblue]If[/COLOR] .Range("A49").Value <> "" [COLOR=darkblue]Then[/COLOR]
            MsgBox "Range A1:A49 is full. ", vbExclamation, "Copy\Paste Canceled"
        [COLOR=darkblue]Else[/COLOR]
          [COLOR=#ff0000]  Sheets("Sheet1").Range("A1:F48").Copy[/COLOR]
[COLOR=#0000ff]            .Range("A50").End(xlUp).Offset(1).PasteSpecial xlPasteValues[/COLOR]
            Application.CutCopyMode = [COLOR=darkblue]False[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

This is perfect, i did forget to mention that i needed to modify this code to be Sheets("Sheet1").Range("A1:A48").Copy

so i will only be copying one row at a time, so with that in mind and edited this is PERFECT!

i guess all that i really needed was the section in blue.

Thanx!

 

[AlphaFrog]

This is perfect, i did forget to mention that i needed to modify this code to be Sheets("Sheet1").Range("A1:A48").Copy

so i will only be copying one row at a time, so with that in mind and edited this is PERFECT!

i guess all that i really needed was the section in blue.

Thanx!

You're welcome.

You do need a little more than just the blue line though. The code should test if the bottom cell A49 is blank or not to determine if all the cells are filled before using .End(xlUp)

 

[deletedalien]

You're welcome.

You do need a little more than just the blue line though. The code should test if the bottom cell A49 is blank or not to determine if all the cells are filled before using .End(xlUp)

Ohh ok that makes sense.

Thanx again

 

[Tarek_CTG]

Can anyone help me here please?

http://www.mrexcel.com/forum/excel-questions/891622-copy-paste-data-immediate-next-blank-row.html

 

[Jack235]

Try

Sheets("Sheet1").Range("A1:F48").Copy
With Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

This is really helpful, thanks. But a bit like the follow up asked in the thread (didn't see an answer to it), how would I modify this code so that subsequent entries are listed in the next available column rather than row?

 

[MARK858]

Assuming that row 1 has the longest row...

Sheets("Sheet1").Range("A1:F48").Copy
With Sheets("Sheet5").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
    .PasteSpecial Paste:=xlPasteColumnWidths
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
End With

 

[Jack235]

thanks. One other question, if i want to use the original code to find the next row but transpose the data what should i add?

 

[MARK858]

, Transpose:=True