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
Blocked Profile - Apr 16, 2018 at 05:00 PM
Related:
- Transfer data from one excel worksheet to another automatically with vba
- Transfer data from one excel worksheet to another automatically - Guide
- Free fire transfer - Guide
- How to automatically transfer data between sheets in Excel - Guide
- Number to words in excel formula without vba - Guide
- Vba case like - Guide
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:
YOu will need to assign a shortcut key to the macro, see examples below.
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.
Apr 12, 2018 at 05:26 PM
Apr 16, 2018 at 02:36 PM
Apr 16, 2018 at 05:00 PM