Copy data in one worksheet to another VBA

Closed
JohnMcLaughlin Posts 10 Registration date Sunday April 8, 2018 Status Member Last seen April 19, 2018 - Apr 8, 2018 at 03:03 PM
 Blocked Profile - Apr 16, 2018 at 05:00 PM
Hi all,

I am trying very hard with little success to automate this with VBA.

I am trying to copy data from Sheet1 Row1 in Column A, B & C and then paste it into Sheet2 Row1 Column A, B & C (paste 5 times) then move back to Sheet1 and copy data in Row 2 Column A, B & C and paste it in to Sheet2, next available row. I am trying to do this until I reach a blank row in Sheet1. Have tried offset, etc but going around in circles.
Any help , advice or guidance would be very much appreciated.

3 responses

OK, here is the code as written, and tested, and runs. Please understand, this code is for you to modify to your model. If you receive an error, I will assist, but I will need a screen shot of the error.

I hope this helps:
Option Explicit

Public sheet1, sheet2 'this makes these SHEETS available to all code in this worksheet
Public rowcountStorageLocation

'#######################################

Private Sub Workbook_Open()
Call Initialize
End Sub

'#######################################

Sub Initialize()
sheet1 = "Sheet1"
sheet2 = "Sheet2"
rowcountStorageLocation = "A1"

'#######################################

ThisWorkbook.Worksheets(sheet1).Select
If Worksheets(sheet1).Range(rowcountStorageLocation).Value = "" Then
Worksheets(sheet1).Range(rowcountStorageLocation).Value = 0
End If
Call updatecounts
End Sub

'#######################################

Sub updatecounts(Optional recount As Boolean)
Dim rowssheet1, rowssheet2

If recount = True Then
ThisWorkbook.Worksheets(sheet2).Select
rowssheet2 = Cells(ThisWorkbook.Worksheets(sheet2).Rows.Count, 1).End(xlUp).Row
Worksheets(sheet2).Range(rowcountStorageLocation).Value = rowssheet2 - 1
Exit Sub
End If

ThisWorkbook.Worksheets(sheet2).Select
rowssheet2 = Cells(ThisWorkbook.Worksheets(sheet2).Rows.Count, 1).End(xlUp).Row
Worksheets(sheet2).Range(rowcountStorageLocation).Value = rowssheet2 - 1

ThisWorkbook.Worksheets(sheet1).Select
rowssheet1 = Cells(ThisWorkbook.Worksheets(sheet1).Rows.Count, 1).End(xlUp).Row
Worksheets(sheet1).Range(rowcountStorageLocation).Value = rowssheet1 - 1
End Sub

'########################################

Sub transferData()
'######################DEPLOYMENT CONSIDERATIONS#################################################
'#after pasting this code, you need to go into the Developer tab, and press the MACROS button. #
'#Select this Subroutine in the MACRO WINDOW, and place a SHORTCUT key to whatever you see fit. #
'# SEE EXAMPLE PICTURES POSTED ON FORUM #
'################################################################################################

Dim sheet1rows, sheet2rows
Dim rowsCnt1, rowsCnt2
Dim StartRow, endingrow
Dim pasteTime

sheet1 = "Sheet1"
sheet2 = "Sheet2"
rowcountStorageLocation = "A1"

'get the number of lines for each sheet
sheet1rows = Worksheets(sheet1).Range(rowcountStorageLocation).Value

'we need to start the new count from the values above, after we count the lines again
ThisWorkbook.Worksheets(sheet1).Select
rowsCnt1 = Cells(ThisWorkbook.Worksheets(sheet1).Rows.Count, 1).End(xlUp).Row - 1

If rowsCnt1 <> sheet1rows Then
MsgBox ("Will Move Data at This Time.")
ThisWorkbook.Worksheets(sheet2).Select
sheet2rows = Cells(ThisWorkbook.Worksheets(sheet2).Rows.Count, 1).End(xlUp).Row + 1

endingrow = rowsCnt1 - sheet1rows
For StartRow = sheet1rows + 1 To sheet1rows + endingrow
ThisWorkbook.Worksheets(sheet1).Select
ActiveSheet.Range("A" & StartRow + 1).EntireRow.Copy
ThisWorkbook.Worksheets(sheet2).Select
ActiveSheet.Paste Destination:=ThisWorkbook.Worksheets(sheet2).Range("A" & sheet2rows & ":" & "A" & sheet2rows + 5)
updatecounts (True)
sheet2rows = Worksheets(sheet2).Range(rowcountStorageLocation).Value + 1
ThisWorkbook.Worksheets(sheet1).Select
Next
ThisWorkbook.Worksheets(sheet1).Range("A1").Select
Call updatecounts
Else
MsgBox ("No New Entry was Found.")
End If
End Sub

'########################################

YOu will need to assign a shortcut key to the macro, see examples below.






1
Johnmclaughlin
Apr 12, 2018 at 05:26 PM
Thanks Mark for this. Much appreciated. Can't wait to study it in the morning. I will let you know how I get on.
0
JohnMcLaughlin Posts 10 Registration date Sunday April 8, 2018 Status Member Last seen April 19, 2018
Apr 16, 2018 at 02:36 PM
perfect!
0
Blocked Profile
Apr 16, 2018 at 05:00 PM
You can mark it as solved if you wish! I am glad you were able to learn the steps to accomplish your task!
0