Issues with pasting on the new sheet

[lianne]

Hello, everyone.

I'm pretty new to VBA and I am trying to write some codes for our daily timekeeping sheet.
I have placed a formula on column A,B, and C to copy my Name, Date Today, and Shift Date to replicate whatever is on E7, H7, and J7 if there's and "end time" on Column E.

However, whenever VBA pastes it on the RawFile Sheet, it copies the formula on Column A even if there's no value in it leaving multiple blank rows when offset. I need to get rid of those blank rows everytime it pastes.

Any help will be greatly appreciated.

Here's the code I'm using.

Sub Save()
  Sheets("DAR").Select
  Range("A9:J34").Select
  'Application.CutCopyMode = False
  Selection.Copy
  Sheets("RawFile").Select
  Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).Select
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False


  End Sub

Post Moved to VBA section by MOD


.

 

[Hui]

Lianne

Firstly, Welcome to the Chandoo.org Forums

I find your macro runs ok and I don't see it copying formulas ?

I would simplify the code as follows

  Sheets("DAR").Range("A9:J34").Copy
 
  Sheets("RawFile").Select
  Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial _
  Paste:=xlPasteValues, _
  Operation:=xlNone, _
  SkipBlanks:=False, _
  Transpose:=False

Can you attach a sample file with a more specific example of the issue?

 

[lianne]

Thank you so much for looking into this, Hui. I appreciate it. The problem is, there are gaps in between when I save the file. I was wondering if it we can make it move up to the next blank cell above it?

I have attached a screenshot. Thank you so much in advance.

 

[Hui]

Are they gaps or do the cells have spaces in them?
Can you post the actual file or even just that worksheet ?

 

[lianne]

Hi, Hui.

Thank you for helping me. Here it is.

 

[Hui]

Change your Save macro as shown below

Sub Save()
Dim LR As Integer

Sheets("DAR").Select
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B8:J" & CStr(LR)).Copy

Sheets("RawFile").Select
Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

ps: I'm getting very good at removing passwords, seeing as you forgot to unlock the VBE

 

[lianne]

Hi, Hui.

Thank you. However, I am still encountering the same problem when i paste the data. It adds spaces in between.

 

[Hui]

Can you please attach a copy of the data you are adding ?

 

[Hui]

Try this

Sub Save()
Dim LR As Integer
Dim i As Long

Sheets("DAR").Select
LR = Range("B" & Rows.Count).End(xlUp).Row
Range("B8:J" & CStr(LR)).Copy

Sheets("RawFile").Select
Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

LR = Range("H" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
  If Cells(i, 8).Value = "" Then Rows(i).Delete Shift:=xlUp
Next i

End Sub

 

[lianne]

Thank you, Hui. But now it's just not doing the offset completely. It's pasting it on top of the old file. I'm glad you're still not giving up on me though. I appreciate your patience.

 

[Marc L]

Hi !

Can you please attach a copy of the data you are adding ?

Within a worksheet, as it should be since initial post !
​

 

[lianne]

Hi, Marc.

Here it is.

 

[Marc L]

« No arm, no chocolate ! »

It seems it's exactly the same workbook as post #3
so it is very not what Hui asked for !
​

 

[Hui]

Pls see attached

I have made changes in the Save and the Worksheet_Change events

 

[lianne]

Oh my gosh, it's working perfectly now. I will never have thought about adding a private sub. I'm new at VBA and I really want to learn more since it's making my life easier. I can't thank you enough, Hui! I'll enroll at chandoo classes so I can further develop my skills on this.

Hui, you're amazing. I'm gonna cry.

 

[Hui]

I Appreciate the kind feedback