Transfer data from one worksheet to another, then moving over three columns.

[KLG519]

Hi there,

I would like to transfer data from one spreadsheet to another, by clicking a button (this part is working). However, the next time the user will enter the details, will be a new month. Ideally, the information will move over three columns and paste again:

Private Sub CommandButton1_Click()
With Sheet2.Cells(Rows.Count, 2).End(x1Up)

.Offset(1, 0).Value = ActiveSheet.Range("b23").Value
.Offset(1, 1).Value = ActiveSheet.Range("h23").Value
.Offset(1, 2).Value = ActiveSheet.Range("b13").Value
.Offset(2, 0).Value = ActiveSheet.Range("c23").Value
.Offset(2, 1).Value = ActiveSheet.Range("i23").Value
.Offset(2, 2).Value = ActiveSheet.Range("c13").Value
.Offset(3, 0).Value = ActiveSheet.Range("d23").Value
.Offset(3, 1).Value = ActiveSheet.Range("j23").Value
.Offset(3, 2).Value = ActiveSheet.Range("d13").Value
.Offset(4, 0).Value = ActiveSheet.Range("e23").Value
.Offset(4, 1).Value = ActiveSheet.Range("k23").Value
.Offset(4, 2).Value = ActiveSheet.Range("e13").Value

End With

End Sub

[JLGWhiz]

See if this does what you want.

Please Login or Register  to view this content.

[KLG519]

Close.

But when I hit the button again (to submit the next month's info). It doesn't transfer at all. Also, when I modified

With Sheet2.Cells(3, Columns.Count).End(xlToLeft).Offset(, 1)
to
With Sheet2.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
(to fit the second page formatting, I cleared all data on page 2, but it stopped transferring data then too).

[JLGWhiz]

Try this modified version. Based on the original sheet 2 layout.

Please Login or Register  to view this content.

[KLG519]

That's a thing of beauty! It worked! Thank-you so much for your help!

[Vraag en antwoord]

Or

Please Login or Register  to view this content.

[JLGWhiz]

That's a thing of beauty! It worked! Thank-you so much for your help!

You're welcome,
regards, JLG