Press enter during Macro

[NormChart55]

Hello,

I have a macro that I am trying to update to press the enter key but I am having issues with it working. It pulls up the input box and has the cells and columns I am splitting by, but I have to press enter in the input box before it moves on. The 2 bold lines below are where I cannot figure out how to add a keystroke for enter. I have added a line right below to try this but it never seemed to do anything so I removed it. The data below is not the whole macro but only up to the lines I am trying to adjust. I am sure its an easy fix, any thoughts? I appreciate any information.

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
On Error Resume Next
[B]Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "$A$1:$H$1", Type:=8)[/B]
If TypeName(xTRg) = "Nothing" Then Exit Sub
[B]Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "$H:$H", Type:=8)[/B]
If TypeName(xVRg) = "Nothing" Then Exit Sub

 

[shinigamilight]

Why are you using input box in the first place? Are you always using the default ranges or you change them all the time?
Try this

Application.SendKeys ("{Enter}")

 

[NormChart55]

Why are you using input box in the first place? Are you always using the default ranges or you change them all the time?
Try this

Application.SendKeys ("{Enter}")

thank you for your response. That was a macro that I found and was able to work with my files that are being split. I did not necessarily need the input box. The default ranges never change as the file being split is always the same. Would I place the application.sendkeys right after this line "If TypeName(xTRg) = "Nothing" Then Exit Sub" or would it need to be placed in the inputbox section?

 

[shinigamilight]

thank you for your response. That was a macro that I found and was able to work with my files that are being split. I did not necessarily need the input box. The default ranges never change as the file being split is always the same. Would I place the application.sendkeys right after this line "If TypeName(xTRg) = "Nothing" Then Exit Sub" or would it need to be placed in the inputbox section?

Then remove the input boxes, input boxes are for dynamic ranges and stuff. I'd personally recommend to convert it all to ranges.
Try changing all the input boxes like this
before

[B]Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "$A$1:$H$1", Type:=8)[/B]

after

[B]Set xTRg = range("A1:H1")[/B]

before

[B]Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "$H:$H", Type:=8)[/B]

after

[B]Set xVRg = range("H:H")[/B]

 

[Joe4]

Please note that formatting like "Bold" and "Color" text do not work within VBA code tags (which is why you see all those characters at the beginning and end of your code).
They will work with Rich code tags, but not XLS or VBA code tags.

For example, trying to make text red color within VBA code tags:

[COLOR=rgb(184, 49, 47)]Set xVRg = range("H:H")[/COLOR]

Making it red within Rich code tags:

Set xVRg = range("H:H")

I see both of you have been trying to do that in your posts.

 

[NormChart55]

Please note that formatting like "Bold" and "Color" text do not work within VBA code tags (which is why you see all those characters at the beginning and end of your code).
They will work with Rich code tags, but not XLS or VBA code tags.

For example, trying to make text red color within VBA code tags:

[COLOR=rgb(184, 49, 47)]Set xVRg = range("H:H")[/COLOR]

Making it red within Rich code tags:

Set xVRg = range("H:H")

I see both of you have been trying to do that in your posts.

thank you, I see that now. much appreciated

 

[NormChart55]

Then remove the input boxes, input boxes are for dynamic ranges and stuff. I'd personally recommend to convert it all to ranges.
Try changing all the input boxes like this
before

[B]Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "$A$1:$H$1", Type:=8)[/B]

after

[B]Set xTRg = range("A1:H1")[/B]

before

[B]Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "$H:$H", Type:=8)[/B]

after

[B]Set xVRg = range("H:H")[/B]

thank you for the insight. Adjusting this to what you provided is doing exactly what I wanted. thanks for the quick assist!