Copy a Range from sheet and paste the next blank line in another s

I have 2 worksheets
One is Named �GN15�
Two is Named �TempTable�
On Sheet �GN15� - I need to simply click a command button in cell W28 and
copy A28:V28 and then
Go To Sheet �TempTable� and paste to the first blank row available.


I tried several scenarios and nothing work and I am running out time on this
project.

Any help would greatly be appreciated.

Private Sub cmdGN15Data1_Click()

Range("A28:v28").Copy
Worksheets("TempTable").Activate


End Sub

--
ca1358

Give this a try...

Private Sub cmdGN15Data1_Click()
dim rngToCopy as range
dim rngToPaste as range

set rngToCopy = sheets("GN15").Range("A28:v28")
set rngToPaste = sheets("TempTable").cells(rows.count,
"A").end(xlUp).offset(1,0)

rngToCopy.copy rngtopaste


End Sub
--
HTH...

Jim Thomlinson


"ca1358" wrote:

> I have 2 worksheets
> One is Named “GN15”
> Two is Named “TempTable”
> On Sheet “GN15” - I need to simply click a command button in cell W28 and
> copy A28:V28 and then
> Go To Sheet “TempTable” and paste to the first blank row available.
>
>
> I tried several scenarios and nothing work and I am running out time on this
> project.
>
> Any help would greatly be appreciated.
>
> Private Sub cmdGN15Data1_Click()
>
> Range("A28:v28").Copy
> Worksheets("TempTable").Activate
>
>
> End Sub
>
> --
> ca1358

Hi,
Try:

Private Sub cmdGN15Data1_Click()
Dim rgDest As Range ' destination range

Range("A28:v28").Copy

'determine last cell:
Set rgDest = Worksheets("TempTable").Range("A1")
Set rgDest = rgDest.EntireColumn.Cells(rgDest.EntireColumn.Cells.Count)
Set rgDest = rgDest.End(xlUp).Offset(1, 0)

'copy/paste
Range("A28:v28").Copy rgDest

End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com>


"ca1358" wrote:

> I have 2 worksheets
> One is Named “GN15”
> Two is Named “TempTable”
> On Sheet “GN15” - I need to simply click a command button in cell W28 and
> copy A28:V28 and then
> Go To Sheet “TempTable” and paste to the first blank row available.
>
>
> I tried several scenarios and nothing work and I am running out time on this
> project.
>
> Any help would greatly be appreciated.
>
> Private Sub cmdGN15Data1_Click()
>
> Range("A28:v28").Copy
> Worksheets("TempTable").Activate
>
>
> End Sub
>
> --
> ca1358

This works. One more question - how do you paste special values?
--
ca1358


"sebastienm" wrote:

> Hi,
> Try:
>
> Private Sub cmdGN15Data1_Click()
> Dim rgDest As Range ' destination range
>
> Range("A28:v28").Copy
>
> 'determine last cell:
> Set rgDest = Worksheets("TempTable").Range("A1")
> Set rgDest = rgDest.EntireColumn.Cells(rgDest.EntireColumn.Cells.Count)
> Set rgDest = rgDest.End(xlUp).Offset(1, 0)
>
> 'copy/paste
> Range("A28:v28").Copy rgDest
>
> End Sub
> --
> Regards,
> Sébastien
> <http://www.ondemandanalysis.com>
>
>
> "ca1358" wrote:
>
> > I have 2 worksheets
> > One is Named “GN15”
> > Two is Named “TempTable”
> > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > copy A28:V28 and then
> > Go To Sheet “TempTable” and paste to the first blank row available.
> >
> >
> > I tried several scenarios and nothing work and I am running out time on this
> > project.
> >
> > Any help would greatly be appreciated.
> >
> > Private Sub cmdGN15Data1_Click()
> >
> > Range("A28:v28").Copy
> > Worksheets("TempTable").Activate
> >
> >
> > End Sub
> >
> > --
> > ca1358

This works. One more question? How do you paste special values?
--
ca1358


"Jim Thomlinson" wrote:

> Give this a try...
>
> Private Sub cmdGN15Data1_Click()
> dim rngToCopy as range
> dim rngToPaste as range
>
> set rngToCopy = sheets("GN15").Range("A28:v28")
> set rngToPaste = sheets("TempTable").cells(rows.count,
> "A").end(xlUp).offset(1,0)
>
> rngToCopy.copy rngtopaste
>
>
> End Sub
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ca1358" wrote:
>
> > I have 2 worksheets
> > One is Named “GN15”
> > Two is Named “TempTable”
> > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > copy A28:V28 and then
> > Go To Sheet “TempTable” and paste to the first blank row available.
> >
> >
> > I tried several scenarios and nothing work and I am running out time on this
> > project.
> >
> > Any help would greatly be appreciated.
> >
> > Private Sub cmdGN15Data1_Click()
> >
> > Range("A28:v28").Copy
> > Worksheets("TempTable").Activate
> >
> >
> > End Sub
> >
> > --
> > ca1358

Private Sub cmdGN15Data1_Click()
Dim rngToCopy As Range
Dim rngToPaste As Range

Set rngToCopy = Sheets("GN15").Range("A28:v28")
Set rngToPaste = Sheets("TempTable").Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)

rngToCopy.Copy
rngToPaste.PasteSpecial xlPasteValues

End Sub

--
HTH...

Jim Thomlinson


"ca1358" wrote:

> This works. One more question? How do you paste special values?
> --
> ca1358
>
>
> "Jim Thomlinson" wrote:
>
> > Give this a try...
> >
> > Private Sub cmdGN15Data1_Click()
> > dim rngToCopy as range
> > dim rngToPaste as range
> >
> > set rngToCopy = sheets("GN15").Range("A28:v28")
> > set rngToPaste = sheets("TempTable").cells(rows.count,
> > "A").end(xlUp).offset(1,0)
> >
> > rngToCopy.copy rngtopaste
> >
> >
> > End Sub
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "ca1358" wrote:
> >
> > > I have 2 worksheets
> > > One is Named “GN15”
> > > Two is Named “TempTable”
> > > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > > copy A28:V28 and then
> > > Go To Sheet “TempTable” and paste to the first blank row available.
> > >
> > >
> > > I tried several scenarios and nothing work and I am running out time on this
> > > project.
> > >
> > > Any help would greatly be appreciated.
> > >
> > > Private Sub cmdGN15Data1_Click()
> > >
> > > Range("A28:v28").Copy
> > > Worksheets("TempTable").Activate
> > >
> > >
> > > End Sub
> > >
> > > --
> > > ca1358

--
I rec'd run-time error "1004"
PasteSpecial method of Range class failed.

Any suggestions.

Thank you for the first part, beening trying for day just to get that part
to work.


ca1358


"Jim Thomlinson" wrote:

> Private Sub cmdGN15Data1_Click()
> Dim rngToCopy As Range
> Dim rngToPaste As Range
>
> Set rngToCopy = Sheets("GN15").Range("A28:v28")
> Set rngToPaste = Sheets("TempTable").Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
>
> rngToCopy.Copy
> rngToPaste.PasteSpecial xlPasteValues
>
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ca1358" wrote:
>
> > This works. One more question? How do you paste special values?
> > --
> > ca1358
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Give this a try...
> > >
> > > Private Sub cmdGN15Data1_Click()
> > > dim rngToCopy as range
> > > dim rngToPaste as range
> > >
> > > set rngToCopy = sheets("GN15").Range("A28:v28")
> > > set rngToPaste = sheets("TempTable").cells(rows.count,
> > > "A").end(xlUp).offset(1,0)
> > >
> > > rngToCopy.copy rngtopaste
> > >
> > >
> > > End Sub
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "ca1358" wrote:
> > >
> > > > I have 2 worksheets
> > > > One is Named “GN15”
> > > > Two is Named “TempTable”
> > > > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > > > copy A28:V28 and then
> > > > Go To Sheet “TempTable” and paste to the first blank row available.
> > > >
> > > >
> > > > I tried several scenarios and nothing work and I am running out time on this
> > > > project.
> > > >
> > > > Any help would greatly be appreciated.
> > > >
> > > > Private Sub cmdGN15Data1_Click()
> > > >
> > > > Range("A28:v28").Copy
> > > > Worksheets("TempTable").Activate
> > > >
> > > >
> > > > End Sub
> > > >
> > > > --
> > > > ca1358

That code works for me. My best guess is that your worksheet TempTable is
protected. If so then try this

Private Sub cmdGN15Data1_Click()
Dim rngToCopy As Range
Dim rngToPaste As Range
Dim wksToPaste as worksheet

Set rngToCopy = Sheets("GN15").Range("A28:v28")
Set wksToPaste = Sheets("TempTable")
Set rngToPaste = wksToPaste.Cells(Rows.Count, _
"A").End(xlUp).Offset(1, 0)

rngToCopy.Copy
wksToPaste.Unprotect "your password"
rngToPaste.PasteSpecial xlPasteValues
wksToPaste.Protect "your password"

Application.CutCopyMode = False
End Sub

--
HTH...

Jim Thomlinson


"ca1358" wrote:

> --
> I rec'd run-time error "1004"
> PasteSpecial method of Range class failed.
>
> Any suggestions.
>
> Thank you for the first part, beening trying for day just to get that part
> to work.
>
>
> ca1358
>
>
> "Jim Thomlinson" wrote:
>
> > Private Sub cmdGN15Data1_Click()
> > Dim rngToCopy As Range
> > Dim rngToPaste As Range
> >
> > Set rngToCopy = Sheets("GN15").Range("A28:v28")
> > Set rngToPaste = Sheets("TempTable").Cells(Rows.Count, _
> > "A").End(xlUp).Offset(1, 0)
> >
> > rngToCopy.Copy
> > rngToPaste.PasteSpecial xlPasteValues
> >
> > End Sub
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "ca1358" wrote:
> >
> > > This works. One more question? How do you paste special values?
> > > --
> > > ca1358
> > >
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Give this a try...
> > > >
> > > > Private Sub cmdGN15Data1_Click()
> > > > dim rngToCopy as range
> > > > dim rngToPaste as range
> > > >
> > > > set rngToCopy = sheets("GN15").Range("A28:v28")
> > > > set rngToPaste = sheets("TempTable").cells(rows.count,
> > > > "A").end(xlUp).offset(1,0)
> > > >
> > > > rngToCopy.copy rngtopaste
> > > >
> > > >
> > > > End Sub
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "ca1358" wrote:
> > > >
> > > > > I have 2 worksheets
> > > > > One is Named “GN15”
> > > > > Two is Named “TempTable”
> > > > > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > > > > copy A28:V28 and then
> > > > > Go To Sheet “TempTable” and paste to the first blank row available.
> > > > >
> > > > >
> > > > > I tried several scenarios and nothing work and I am running out time on this
> > > > > project.
> > > > >
> > > > > Any help would greatly be appreciated.
> > > > >
> > > > > Private Sub cmdGN15Data1_Click()
> > > > >
> > > > > Range("A28:v28").Copy
> > > > > Worksheets("TempTable").Activate
> > > > >
> > > > >
> > > > > End Sub
> > > > >
> > > > > --
> > > > > ca1358

You were right about the password but now I have another problem. I did not
build this original project but ask to add a piece to this project. The page
that I am copying from has merged cells. Is there any way to get around the
problem? I have tried copying the template with the merge cells to the
TempTable sheet but it didn’t work. The following error occurs

“This operation requires the merged cells to be identically sized.”

Is there any way around it?

Thanks for all you help and replying back so promptly.

--
ca1358


"Jim Thomlinson" wrote:

> That code works for me. My best guess is that your worksheet TempTable is
> protected. If so then try this
>
> Private Sub cmdGN15Data1_Click()
> Dim rngToCopy As Range
> Dim rngToPaste As Range
> Dim wksToPaste as worksheet
>
> Set rngToCopy = Sheets("GN15").Range("A28:v28")
> Set wksToPaste = Sheets("TempTable")
> Set rngToPaste = wksToPaste.Cells(Rows.Count, _
> "A").End(xlUp).Offset(1, 0)
>
> rngToCopy.Copy
> wksToPaste.Unprotect "your password"
> rngToPaste.PasteSpecial xlPasteValues
> wksToPaste.Protect "your password"
>
> Application.CutCopyMode = False
> End Sub
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "ca1358" wrote:
>
> > --
> > I rec'd run-time error "1004"
> > PasteSpecial method of Range class failed.
> >
> > Any suggestions.
> >
> > Thank you for the first part, beening trying for day just to get that part
> > to work.
> >
> >
> > ca1358
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Private Sub cmdGN15Data1_Click()
> > > Dim rngToCopy As Range
> > > Dim rngToPaste As Range
> > >
> > > Set rngToCopy = Sheets("GN15").Range("A28:v28")
> > > Set rngToPaste = Sheets("TempTable").Cells(Rows.Count, _
> > > "A").End(xlUp).Offset(1, 0)
> > >
> > > rngToCopy.Copy
> > > rngToPaste.PasteSpecial xlPasteValues
> > >
> > > End Sub
> > >
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "ca1358" wrote:
> > >
> > > > This works. One more question? How do you paste special values?
> > > > --
> > > > ca1358
> > > >
> > > >
> > > > "Jim Thomlinson" wrote:
> > > >
> > > > > Give this a try...
> > > > >
> > > > > Private Sub cmdGN15Data1_Click()
> > > > > dim rngToCopy as range
> > > > > dim rngToPaste as range
> > > > >
> > > > > set rngToCopy = sheets("GN15").Range("A28:v28")
> > > > > set rngToPaste = sheets("TempTable").cells(rows.count,
> > > > > "A").end(xlUp).offset(1,0)
> > > > >
> > > > > rngToCopy.copy rngtopaste
> > > > >
> > > > >
> > > > > End Sub
> > > > > --
> > > > > HTH...
> > > > >
> > > > > Jim Thomlinson
> > > > >
> > > > >
> > > > > "ca1358" wrote:
> > > > >
> > > > > > I have 2 worksheets
> > > > > > One is Named “GN15”
> > > > > > Two is Named “TempTable”
> > > > > > On Sheet “GN15” - I need to simply click a command button in cell W28 and
> > > > > > copy A28:V28 and then
> > > > > > Go To Sheet “TempTable” and paste to the first blank row available.
> > > > > >
> > > > > >
> > > > > > I tried several scenarios and nothing work and I am running out time on this
> > > > > > project.
> > > > > >
> > > > > > Any help would greatly be appreciated.
> > > > > >
> > > > > > Private Sub cmdGN15Data1_Click()
> > > > > >
> > > > > > Range("A28:v28").Copy
> > > > > > Worksheets("TempTable").Activate
> > > > > >
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > --
> > > > > > ca1358

yes there is a way to get around merged cells, never use them on a sheet in which you intend to use VBA code. Not trying to be wiseguy but merged cells create too many difficulties. I only use them in header rows