I am using Excel 365, and have a workbook with several worksheets. I am trying to get the data that is typed into my "EntryChecklist" unlocked cells into the next empty row on the
"Data" ws. I currently have a button to do this, but ideally, I'd like for the data that is being entered into the cells on the "EntryChecklist" ws to concurrently be input into the
respective cells of the "Data" ws.
The code I have throws a Run-time error '1004' Method 'Range' of object'_Worksheet' failed when I click the button. I am still new to VBA and am struggling to fix the error. Here's what I have:
Sub CopyEntryCL()
' Get the correct worksheet
Dim shRead As Worksheet
Dim shWrite As Worksheet
Dim FirstEmptyRow As Long
Set shRead = ThisWorkbook.Worksheets("EntryChecklist")
Set shWrite = ThisWorkbook.Worksheets("Data")
' selects the next empty row
' ignores blanks inbetween the data set
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
' Copy data from cell B3 on the EntryChecklist worksheet to cell D of the first empty row in the Data worksheet
shRead.Range("B3").Copy
shWrite.Range("D").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D3 on the EntryChecklist worksheet to cell F of the empty row in the Data worksheet
shRead.Range("D3").Copy
shWrite.Range("F").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F3 on the EntryChecklist worksheet to cell A of the empty row in the Data worksheet
shRead.Range("F3").Copy
shWrite.Range("A").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B6 on the EntryChecklist worksheet to cell G of the empty row in the Data worksheet
shRead.Range("B6").Copy
shWrite.Range("G").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D6 on the EntryChecklist worksheet to cell I of the empty row in the Data worksheet
shRead.Range("D6").Copy
shWrite.Range("I").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F6 on the EntryChecklist worksheet to cell B of the empty row in the Data worksheet
shRead.Range("F6").Copy
shWrite.Range("B").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B9 on the EntryChecklist worksheet to cell J of the empty row in the Data worksheet
shRead.Range("B9").Copy
shWrite.Range("J").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D9 on the EntryChecklist worksheet to cell L of the empty row in the Data worksheet
shRead.Range("D9").Copy
shWrite.Range("L").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F9 on the EntryChecklist worksheet to cell C of the empty row in the Data worksheet
shRead.Range("F9").Copy
shWrite.Range("FC").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B24 on the EntryChecklist worksheet to cell Mof the empty row in the Data worksheet
shRead.Range("B24").Copy
shWrite.Range("M").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D24 on the EntryChecklist worksheet to cell N of the empty row in the Data worksheet
shRead.Range("D24").Copy
shWrite.Range("N").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B30 on the EntryChecklist worksheet to cell O of the empty row in the Data worksheet
shRead.Range("B30").Copy
shWrite.Range("O").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B33 on the EntryChecklist worksheet to cell P of the empty row in the Data worksheet
shRead.Range("B33").Copy
shWrite.Range("P").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D30 on the EntryChecklist worksheet to cell Q of the empty row in the Data worksheet
shRead.Range("D30").Copy
shWrite.Range("Q").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F29 on the EntryChecklist worksheet to cell R of the empty row in the Data worksheet
shRead.Range("F29").Copy
shWrite.Range("R").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F31 on the EntryChecklist worksheet to cell S of the empty row in the Data worksheet
shRead.Range("F31").Copy
shWrite.Range("S").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
I am sure there is also a cleaner way to do this. I've included screen shots of my two worksheets as well.
Thank you in advance!
"Data" ws. I currently have a button to do this, but ideally, I'd like for the data that is being entered into the cells on the "EntryChecklist" ws to concurrently be input into the
respective cells of the "Data" ws.
The code I have throws a Run-time error '1004' Method 'Range' of object'_Worksheet' failed when I click the button. I am still new to VBA and am struggling to fix the error. Here's what I have:
Sub CopyEntryCL()
' Get the correct worksheet
Dim shRead As Worksheet
Dim shWrite As Worksheet
Dim FirstEmptyRow As Long
Set shRead = ThisWorkbook.Worksheets("EntryChecklist")
Set shWrite = ThisWorkbook.Worksheets("Data")
' selects the next empty row
' ignores blanks inbetween the data set
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
' Copy data from cell B3 on the EntryChecklist worksheet to cell D of the first empty row in the Data worksheet
shRead.Range("B3").Copy
shWrite.Range("D").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D3 on the EntryChecklist worksheet to cell F of the empty row in the Data worksheet
shRead.Range("D3").Copy
shWrite.Range("F").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F3 on the EntryChecklist worksheet to cell A of the empty row in the Data worksheet
shRead.Range("F3").Copy
shWrite.Range("A").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B6 on the EntryChecklist worksheet to cell G of the empty row in the Data worksheet
shRead.Range("B6").Copy
shWrite.Range("G").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D6 on the EntryChecklist worksheet to cell I of the empty row in the Data worksheet
shRead.Range("D6").Copy
shWrite.Range("I").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F6 on the EntryChecklist worksheet to cell B of the empty row in the Data worksheet
shRead.Range("F6").Copy
shWrite.Range("B").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B9 on the EntryChecklist worksheet to cell J of the empty row in the Data worksheet
shRead.Range("B9").Copy
shWrite.Range("J").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D9 on the EntryChecklist worksheet to cell L of the empty row in the Data worksheet
shRead.Range("D9").Copy
shWrite.Range("L").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F9 on the EntryChecklist worksheet to cell C of the empty row in the Data worksheet
shRead.Range("F9").Copy
shWrite.Range("FC").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B24 on the EntryChecklist worksheet to cell Mof the empty row in the Data worksheet
shRead.Range("B24").Copy
shWrite.Range("M").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D24 on the EntryChecklist worksheet to cell N of the empty row in the Data worksheet
shRead.Range("D24").Copy
shWrite.Range("N").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B30 on the EntryChecklist worksheet to cell O of the empty row in the Data worksheet
shRead.Range("B30").Copy
shWrite.Range("O").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell B33 on the EntryChecklist worksheet to cell P of the empty row in the Data worksheet
shRead.Range("B33").Copy
shWrite.Range("P").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell D30 on the EntryChecklist worksheet to cell Q of the empty row in the Data worksheet
shRead.Range("D30").Copy
shWrite.Range("Q").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F29 on the EntryChecklist worksheet to cell R of the empty row in the Data worksheet
shRead.Range("F29").Copy
shWrite.Range("R").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
' Copy data from cell F31 on the EntryChecklist worksheet to cell S of the empty row in the Data worksheet
shRead.Range("F31").Copy
shWrite.Range("S").PasteSpecial xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
End Sub
I am sure there is also a cleaner way to do this. I've included screen shots of my two worksheets as well.
Thank you in advance!