Prev: vbe command for making excel in the active window on top
Next: Screen repainting issue
From: Jock on 5 May 2010 11:34
The following should work but it only copies cell A1 from the SSh (source) to
cell A1 on the DSh (destination).

Sub MoveData()

Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String

Set SSh = ActiveWorkbook.ActiveSheet
Set DSh = Workbooks("CMS Register of ClaimsAuto").Worksheets("Summary")

CopyRange = SSh.Range("A2:E" & Rows.count).End(xlUp).Copy

DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

Where did I go wrong?

--
Traa Dy Liooar

Jock
From: Mike H on 5 May 2010 11:51
Jock,

Try this

Sub MoveData()

Dim SSh As Worksheet 'source sheet
Dim DSh As Worksheet 'target sheet
Dim LastRow As Long
Dim CopyRange As String
Set SSh = ActiveWorkbook.ActiveSheet
Set DSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
LastRow = SSh.Cells(Cells.Rows.Count, "E").End(xlUp).Row
SSh.Range("A2:E" & LastRow).Copy
LastRow = DSh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jock" wrote:

> The following should work but it only copies cell A1 from the SSh (source) to
> cell A1 on the DSh (destination).
>
> Sub MoveData()
>
> Dim SSh As Worksheet 'source sheet
> Dim DSh As Worksheet 'target sheet
> Dim LastRow As Long
> Dim CopyRange As String
>
> Set SSh = ActiveWorkbook.ActiveSheet
> Set DSh = Workbooks("CMS Register of ClaimsAuto").Worksheets("Summary")
>
> CopyRange = SSh.Range("A2:E" & Rows.count).End(xlUp).Copy
>
> DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
> End Sub
>
> Where did I go wrong?
>
> --
> Traa Dy Liooar
>
> Jock
From: Jock on 6 May 2010 05:43
Almost perfect.
This procedure will copy bold text from the source sheet to the destination
sheet. Just prior to that happening, I need all text on the Dest sht from row
4 onwards to be bold = False. How would that be done?
--
Traa Dy Liooar

Jock


"Mike H" wrote:

> Jock,
>
> Try this
>
> Sub MoveData()
>
> Dim SSh As Worksheet 'source sheet
> Dim DSh As Worksheet 'target sheet
> Dim LastRow As Long
> Dim CopyRange As String
> Set SSh = ActiveWorkbook.ActiveSheet
> Set DSh = Workbooks("CMS Register of ClaimsAuto.xlsx").Worksheets("Summary")
> LastRow = SSh.Cells(Cells.Rows.Count, "E").End(xlUp).Row
> SSh.Range("A2:E" & LastRow).Copy
> LastRow = DSh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
> End Sub
>
> --
> Mike
>
> When competing hypotheses are otherwise equal, adopt the hypothesis that
> introduces the fewest assumptions while still sufficiently answering the
> question.
>
>
> "Jock" wrote:
>
> > The following should work but it only copies cell A1 from the SSh (source) to
> > cell A1 on the DSh (destination).
> >
> > Sub MoveData()
> >
> > Dim SSh As Worksheet 'source sheet
> > Dim DSh As Worksheet 'target sheet
> > Dim LastRow As Long
> > Dim CopyRange As String
> >
> > Set SSh = ActiveWorkbook.ActiveSheet
> > Set DSh = Workbooks("CMS Register of ClaimsAuto").Worksheets("Summary")
> >
> > CopyRange = SSh.Range("A2:E" & Rows.count).End(xlUp).Copy
> >
> > DSh.Range("A" & LastRow + 1).PasteSpecial (xlPasteValues)
> > End Sub
> >
> > Where did I go wrong?
> >
> > --
> > Traa Dy Liooar
> >
> > Jock
 | 
Pages: 1
Prev: vbe command for making excel in the active window on top
Next: Screen repainting issue