Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Store Data Macro

0

Hello again,

I have a macro that copies information from a form, with a submit button, on one sheet and posts the data to another sheet.  When I hit submit, it transfers the data to the new sheet and clears the form, however it will always over-write the existing data on the target sheet instead of finding the blank row.

Column A in my target sheet is empty.  All Data on my target sheet is in column B-I.

Thank you.

Sub Store_Data()

' Michael is the coolest

' Takes data from one worksheet and stores in the next empty row on another Worksheet

Dim sourceSheet As Worksheet

Dim dataSheet As Worksheet

Dim nextRow As Long

' Make some sheet variables so we can use those instead of hard-coding sheet reference

Set sourceSheet = Sheets("Request")

Set dataSheet = Sheets("TimeOff")

' Get the next empty row from the Data sheet.

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

'Input the form values into the Data Sheet.

dataSheet.Cells(nextRow, 2).Value = sourceSheet.Range("C4").Value

dataSheet.Cells(nextRow, 3).Value = sourceSheet.Range("F4").Value

dataSheet.Cells(nextRow, 4).Value = sourceSheet.Range("C6").Value

dataSheet.Cells(nextRow, 5).Value = sourceSheet.Range("F6").Value

dataSheet.Cells(nextRow, 6).Value = sourceSheet.Range("C8").Value

dataSheet.Cells(nextRow, 7).Value = sourceSheet.Range("F8").Value

dataSheet.Cells(nextRow, 8).Value = sourceSheet.Range("C10").Value

dataSheet.Cells(nextRow, 9).Value = sourceSheet.Range("C12").Value

'Clear Date

sourceSheet.Range("C4").Value = ""

sourceSheet.Range("F4").Value = ""

sourceSheet.Range("C6").Value = ""

sourceSheet.Range("F6").Value = ""

sourceSheet.Range("C8").Value = ""

sourceSheet.Range("F8").Value = ""

sourceSheet.Range("C10").Value = ""

sourceSheet.Range("C12").Value = ""

End Sub

excel
posted by: Galanthias (rep: 4) on Mon Jul 22, 2024 at 7:10 am
Answer
Discuss

Discussion

Hi again Galanthias. There was no need to repeat your question (as new) but both Willie and I said we need your file before we can help. Please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data  and macros. Then we should be able to give specific help.

For example, we don't know if youhave data in column A of your dataSheet- if not, the line:

nextRow = dataSheet.Range("A" & dataSheet.Rows.Count).End(xlUp).Offset(1).Row

will always return nextRow as 2 (and may lead to overwriting).
John_Ru (rep: 6417) Jul 22, '24 at 7:24 am
Ok, I uploaded a copy of the target "datasheet".  Hope this helps.
Galanthias (rep: 4) Jul 22, '24 at 11:43 am
The file helped but I'd got lucky and guessed the problem- see Willie's Answer.
John_Ru (rep: 6417) Jul 22, '24 at 12:54 pm
Add to Discussion

Answers

0
Selected Answer

Hello Galanthias,

Your code takes data from the "Request" sheet (sourceSheet) and inputs it to the "TimeOff" sheet (dataSheet). Your code does not input anything to column "A" so, as John has pointed out, with column "A" being empty your code will always give nextRow a value of 2. Your code adds data to columns "B" thru "I" so you should change your code to: (note change in bold, also it is not necessary to include "dataSheet" before Rows.Count)

nextRow = dataSheet.Range("B" & Rows.Count).End(xlUp).Offset(1).Row

Making this one small change will prevent overwriting of existing data. (Including the complete code is what made the difference in providing a solution.)

If this solves your problem please mark my answer as Selected

Cheers   :-)

posted by: WillieD24 (rep: 587) on Mon Jul 22, 2024 at 10:08 am
Discuss

Discussion

@Willie - looks like my guess may pay off for you!
John_Ru (rep: 6417) Jul 22, '24 at 12:49 pm
Thank you.  I thought I had tried that, but for some reason it now works.  Much appreciated
Galanthias (rep: 4) Jul 22, '24 at 1:00 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login