Adapt codes

mdorey

Board Regular
Joined
Oct 6, 2011
Messages
64
I want to put this code
Code:
Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
in workbook1, workbook1 will open several workbooks (six) with vba.

How can i adapt that code to select the range from the other workbooks and past on workbook1.sheet(named with the name of the files open)?

Here is a sample of the code
Code:
Sub OpenData()


Dim myValue As Variant
myValue = InputBox("File name?")


    ChDir "L:\..."
    Workbooks.OpenText Filename:= _
        "L:\...\PRO " & myValue & ".txt" _
        , Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hello,

If im understanding you question correctly you would like to run that same line of code in all 6 newly opened files if so just add this line after opening the files

Code:
Windows("File1.xlsx").Activate
Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
Windows("OriginalFile.xlsx").Activate
'Insert your pasting code'
Windows("File2.xlsx").Activate
Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
Windows("OriginalFile.xlsx").Activate
'Insert your pasting code'
.
.
.
.
and so on for all 6 of them

try that see if that works
 
Upvote 0
Hello,

If im understanding you question correctly you would like to run that same line of code in all 6 newly opened files if so just add this line after opening the files

Code:
Windows("File1.xlsx").Activate
Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
Windows("OriginalFile.xlsx").Activate
'Insert your pasting code'
Windows("File2.xlsx").Activate
Rows("2:" & Range([A1], ActiveSheet.UsedRange).Rows.Count - 2).Copy
Windows("OriginalFile.xlsx").Activate
'Insert your pasting code'
.
.
.
.
and so on for all 6 of them

try that see if that works

Thank you very much for the reply :)
For now i cant try the code, unfortunately i'm still looking for the last parte of the code that will detect in which line of the wookbook1 will the data be past...

I have to find a way to search by vba the line column B(in wookbook1.sheet(named the same name has the file opened)) that have the same value of the second line from the column B of the file that the data will be copy... :P

is it possible for you to help me on this as well???

Many thanks
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top