Copy values to new sheet / next empty row

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
Hi there, I have the following code. It copies values from sheet 1 to sheet 2, with the range of A2:J999. The issue, is that the range is actually dynamic. Sometimes, I just need A2:J10, other times, A2:J25.

Should I be performing this function using a different method? Throughout this function, I also want to add a value "Not Exported" to column K. Is it easy to append this value to each row as part of this function?

Thank you all very much, in advance :) Have a great night.

Code:
Function CopyResults()

  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet


  Set copySheet = Worksheets("1")
  Set pasteSheet = Worksheets("2")


  copySheet.Range("A2:J999").Copy
  pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  End Function
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
You can't move rows with a Function.
A function will only return a result, not carry out actions.

Try

Code:
Sub MM1()
Application.ScreenUpdating = False
Dim WS1 As Worksheet, WS2 As Worksheet, lr As Long

Set WS1 = Worksheets("1")
Set WS2 = Worksheets("2")
lr = WS1.Cells(Rows.Count, "J").End(xlUp).Row

WS1.Range("K2:K" & lr).Value = "Not Exported"
WS1.Range("A2:J" & lr).Copy
WS2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
What do you mean by not being able to move rows? I'm just trying to copy. Any idea on appending the word not exported? Maybe I should create a function for that before it copies data over to the other sheet.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,882
Office Version
  1. 2013
Platform
  1. Windows
I'm curious.
You said:
Hi there, I have the following code. It copies values from sheet 1 to sheet 2, with the range of A2:J999.

How is it possible you have a Function copying data to another sheet?

What do you mean by not being able to move rows? I'm just trying to copy. Any idea on appending the word not exported? Maybe I should create a function for that before it copies data over to the other sheet.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

See the 2nd line of my post...copy OR move
A function will only return a result, not carry out actions.

Code:
Any idea on appending the word not exported?

I have included the line for that in the code.

Have you tried the code I posted ?
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
You got me... no I didn't. Added a couple items for formatting... and this works great. I have an off topic question, not sure if there is an answer.

Earlier in my macro I do text to columns to split out some text. And later on, if I copy more text in a similar format, it is as if the text to column setting is cached, as it parses the new data. Is there any way to prevent this from happening without exiting excel completely?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Maybe you should post ALL the code involved.
But normally, the text to columns doesn't set to any particular setting
 

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
I figured it out :) Did a new text to column, and made delimited by space = false, which turned this feature off.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,281
Office Version
  1. 2013
Platform
  1. Windows
OK...glad you got it sorted AND thanks for sharing the solution...(y)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,540
Messages
5,529,434
Members
409,876
Latest member
Akash Yadav
Top