Copy values to new sheet / next empty row

[srosk]

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.

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

 

[Michael M]

You can't move rows with a Function.
A function will only return a result, not carry out actions.

Try

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]

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]

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]

See the 2nd line of my post...copy OR move

A function will only return a result, not carry out actions.

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]

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]

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

 

[srosk]

See here: https://www.extendoffice.com/documents/excel/2511-excel-prevent-text-to-column.html

Basically, when you text to column, excel saves the settings so when you go to paste new data, it parses it based on delimited settings. Is there any way to pre-emotively fix this after doing a text to column, such as with VBA coding?

 

[srosk]

I figured it out Did a new text to column, and made delimited by space = false, which turned this feature off.

 

[Michael M]

OK...glad you got it sorted AND thanks for sharing the solution...