Filter table, copy and paste into multiple existing workbooksCopy specific data from one workbook and paste it into another workbook (paste from second row)Lookup from and loop through workbooks and copy value if there is a match to main workbook to main workbookConditional copy Excel File-2 data to excel file-1?vba copy value if cell on workbook A matches cell on workbook B?Copy cells in adding a row in another workbookLoop through excel spreadsheets in different workbooksTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopRun-time error '1004' When Using Excel-VBA Macro to Copy From WorkbookCopy certain range from all closed workbooks within folder on desktop and paste it to current (master) workbookVBA not working (copy data from one file and paste to different workbook below last row of data)

Sort with assumptions

Make a Bowl of Alphabet Soup

Why do Radio Buttons not fill the entire outer circle?

Why would five hundred and five same as one?

Is this saw blade faulty?

Output visual diagram of picture

Travelling in US for more than 90 days

Has the laser at Magurele, Romania reached a tenth of the Sun's power?

What properties make a magic weapon befit a Rogue more than a DEX-based Fighter?

How to preserve electronics (computers, ipads, phones) for hundreds of years?

Why didn't Voldemort know what Grindelwald looked like?

How can I, as DM, avoid the Conga Line of Death occurring when implementing some form of flanking rule?

Connection Between Knot Theory and Number Theory

A seasonal riddle

Highest stage count that are used one right after the other?

Does capillary rise violate hydrostatic paradox?

Derivative of an interpolated function

What is this high flying aircraft over Pennsylvania?

What is the period/term used describe Giuseppe Arcimboldo's style of painting?

Hashing password to increase entropy

I keep switching characters, how do I stop?

"Marked down as someone wanting to sell shares." What does that mean?

Why is participating in the European Parliamentary elections used as a threat?

What is the tangent at a sharp point on a curve?



Filter table, copy and paste into multiple existing workbooks


Copy specific data from one workbook and paste it into another workbook (paste from second row)Lookup from and loop through workbooks and copy value if there is a match to main workbook to main workbookConditional copy Excel File-2 data to excel file-1?vba copy value if cell on workbook A matches cell on workbook B?Copy cells in adding a row in another workbookLoop through excel spreadsheets in different workbooksTake the date in one worksheet and find the same date in another worksheet column and return the cell reference for that date to use in a loopRun-time error '1004' When Using Excel-VBA Macro to Copy From WorkbookCopy certain range from all closed workbooks within folder on desktop and paste it to current (master) workbookVBA not working (copy data from one file and paste to different workbook below last row of data)













0















I have a table that has data for multiple projects.



I need to filter the data (by project number), then copy and paste each project's data into separate existing workbooks ( I have a tab that contains file paths of the each workbook and project names.



I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that.



I know there is an issue but not too sure how to fix it.



Can anyone help?



Below is a link to the files



enter link description here



Sub OpenProjects()


Dim N As String
Dim LAST As Integer
Dim TABLE As Range
Dim PNumber As String 'Project Number



LAST = Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row


Set TABLE = Sheets("Projects").Range("A1:M" & LAST)


'Open files

Sheets("Unique Projects").Select

RowCount = Application.WorksheetFunction.CountA(Sheets("unique Projects").Range("B:B"))


For i = 2 To RowCount

N = Sheets("Unique projects").Cells(i, 2)

Workbooks.Open (N)



 'Back to original workbook



Workbooks("original.xlsm").Activate

For Each NUMBER In Sheets("Unique Projects").Range([A2], Cells(Rows.Count, "A").End(xlUp))

With TABLE
.AutoFilter
.AutoFilter Field:=1, Criteria1:=NUMBER.Value
.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks(NUMBER & ".xlsm").Sheets("sheet1").Range("A1")

End With

Workbooks(NUMBER & ".xlsm").Save
Workbooks(NUMBER & ".xlsm").Close

Workbooks("Original").Activate



Next NUMBER


Next i
End Sub





excel vba







share|improve this question
























  • where do you get PNumber from?

    – Ricardo Diaz
    Mar 7 at 1:24











  • Sorry, that should not be there.

    – Harley C
    Mar 7 at 1:26






  • 1





    Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

    – Ricardo Diaz
    Mar 7 at 1:31











  • Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

    – Variatus
    Mar 7 at 1:40











  • What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

    – Ricardo Diaz
    Mar 7 at 2:19















0















I have a table that has data for multiple projects.



I need to filter the data (by project number), then copy and paste each project's data into separate existing workbooks ( I have a tab that contains file paths of the each workbook and project names.



I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that.



I know there is an issue but not too sure how to fix it.



Can anyone help?



Below is a link to the files



enter link description here



Sub OpenProjects()


Dim N As String
Dim LAST As Integer
Dim TABLE As Range
Dim PNumber As String 'Project Number



LAST = Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row


Set TABLE = Sheets("Projects").Range("A1:M" & LAST)


'Open files

Sheets("Unique Projects").Select

RowCount = Application.WorksheetFunction.CountA(Sheets("unique Projects").Range("B:B"))


For i = 2 To RowCount

N = Sheets("Unique projects").Cells(i, 2)

Workbooks.Open (N)



 'Back to original workbook



Workbooks("original.xlsm").Activate

For Each NUMBER In Sheets("Unique Projects").Range([A2], Cells(Rows.Count, "A").End(xlUp))

With TABLE
.AutoFilter
.AutoFilter Field:=1, Criteria1:=NUMBER.Value
.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks(NUMBER & ".xlsm").Sheets("sheet1").Range("A1")

End With

Workbooks(NUMBER & ".xlsm").Save
Workbooks(NUMBER & ".xlsm").Close

Workbooks("Original").Activate



Next NUMBER


Next i
End Sub





excel vba







share|improve this question
























  • where do you get PNumber from?

    – Ricardo Diaz
    Mar 7 at 1:24











  • Sorry, that should not be there.

    – Harley C
    Mar 7 at 1:26






  • 1





    Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

    – Ricardo Diaz
    Mar 7 at 1:31











  • Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

    – Variatus
    Mar 7 at 1:40











  • What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

    – Ricardo Diaz
    Mar 7 at 2:19













0












0








0








I have a table that has data for multiple projects.



I need to filter the data (by project number), then copy and paste each project's data into separate existing workbooks ( I have a tab that contains file paths of the each workbook and project names.



I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that.



I know there is an issue but not too sure how to fix it.



Can anyone help?



Below is a link to the files



enter link description here



Sub OpenProjects()


Dim N As String
Dim LAST As Integer
Dim TABLE As Range
Dim PNumber As String 'Project Number



LAST = Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row


Set TABLE = Sheets("Projects").Range("A1:M" & LAST)


'Open files

Sheets("Unique Projects").Select

RowCount = Application.WorksheetFunction.CountA(Sheets("unique Projects").Range("B:B"))


For i = 2 To RowCount

N = Sheets("Unique projects").Cells(i, 2)

Workbooks.Open (N)



 'Back to original workbook



Workbooks("original.xlsm").Activate

For Each NUMBER In Sheets("Unique Projects").Range([A2], Cells(Rows.Count, "A").End(xlUp))

With TABLE
.AutoFilter
.AutoFilter Field:=1, Criteria1:=NUMBER.Value
.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks(NUMBER & ".xlsm").Sheets("sheet1").Range("A1")

End With

Workbooks(NUMBER & ".xlsm").Save
Workbooks(NUMBER & ".xlsm").Close

Workbooks("Original").Activate



Next NUMBER


Next i
End Sub





excel vba







share|improve this question
















I have a table that has data for multiple projects.



I need to filter the data (by project number), then copy and paste each project's data into separate existing workbooks ( I have a tab that contains file paths of the each workbook and project names.



I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that.



I know there is an issue but not too sure how to fix it.



Can anyone help?



Below is a link to the files



enter link description here



Sub OpenProjects()


Dim N As String
Dim LAST As Integer
Dim TABLE As Range
Dim PNumber As String 'Project Number



LAST = Sheets("Projects").Cells(Rows.Count, "A").End(xlUp).Row


Set TABLE = Sheets("Projects").Range("A1:M" & LAST)


'Open files

Sheets("Unique Projects").Select

RowCount = Application.WorksheetFunction.CountA(Sheets("unique Projects").Range("B:B"))


For i = 2 To RowCount

N = Sheets("Unique projects").Cells(i, 2)

Workbooks.Open (N)



 'Back to original workbook



Workbooks("original.xlsm").Activate

For Each NUMBER In Sheets("Unique Projects").Range([A2], Cells(Rows.Count, "A").End(xlUp))

With TABLE
.AutoFilter
.AutoFilter Field:=1, Criteria1:=NUMBER.Value
.SpecialCells(xlCellTypeVisible).Copy Destination:=Workbooks(NUMBER & ".xlsm").Sheets("sheet1").Range("A1")

End With

Workbooks(NUMBER & ".xlsm").Save
Workbooks(NUMBER & ".xlsm").Close

Workbooks("Original").Activate



Next NUMBER


Next i
End Sub





excel vba




excel vba






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Mar 7 at 4:01









BigBen

6,6522719




6,6522719










asked Mar 7 at 1:19









Harley CHarley C

14




14












  • where do you get PNumber from?

    – Ricardo Diaz
    Mar 7 at 1:24











  • Sorry, that should not be there.

    – Harley C
    Mar 7 at 1:26






  • 1





    Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

    – Ricardo Diaz
    Mar 7 at 1:31











  • Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

    – Variatus
    Mar 7 at 1:40











  • What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

    – Ricardo Diaz
    Mar 7 at 2:19

















  • where do you get PNumber from?

    – Ricardo Diaz
    Mar 7 at 1:24











  • Sorry, that should not be there.

    – Harley C
    Mar 7 at 1:26






  • 1





    Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

    – Ricardo Diaz
    Mar 7 at 1:31











  • Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

    – Variatus
    Mar 7 at 1:40











  • What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

    – Ricardo Diaz
    Mar 7 at 2:19
















where do you get PNumber from?

– Ricardo Diaz
Mar 7 at 1:24





where do you get PNumber from?

– Ricardo Diaz
Mar 7 at 1:24













Sorry, that should not be there.

– Harley C
Mar 7 at 1:26





Sorry, that should not be there.

– Harley C
Mar 7 at 1:26




1




1





Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

– Ricardo Diaz
Mar 7 at 1:31





Then what is NUMBER? initially it'd be the cell in the range. You could use option explicit at the top of the module so you prevent from using undeclared variables. in this case, seems like you could have an empty cell and maybe that is raising the error. One suggestion: 1) edit your question and add a screenshot of the data (or a sample) where and how you have project names and paths

– Ricardo Diaz
Mar 7 at 1:31













Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

– Variatus
Mar 7 at 1:40





Number is defined as a String. In For Each Number Number is an object because Each is always an object. Select Number in your code and press F1 to learn which object you are referring to. It isn't what you hoped to do. When you assign names to your variables, especially short, convenient and obvious names, always use F1 to check whether a MS engineer had the same idea before you and you are, in fact, using a reserved word.

– Variatus
Mar 7 at 1:40













What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

– Ricardo Diaz
Mar 7 at 2:19





What error? in what line? "I have managed to open the first project's workbook, copy and paste, save and close the workbook but excel throw an error after that."

– Ricardo Diaz
Mar 7 at 2:19












0






active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55034625%2ffilter-table-copy-and-paste-into-multiple-existing-workbooks%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























0






active

oldest

votes








0






active

oldest

votes









active

oldest

votes






active

oldest

votes















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid …


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55034625%2ffilter-table-copy-and-paste-into-multiple-existing-workbooks%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







-

Popular posts from this blog

Save data to MySQL database using ExtJS and PHP [closed]2019 Community Moderator ElectionHow can I prevent SQL injection in PHP?Which MySQL data type to use for storing boolean valuesPHP: Delete an element from an arrayHow do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?How to get a list of MySQL user accountsHow Do You Parse and Process HTML/XML in PHP?Reference — What does this symbol mean in PHP?How does PHP 'foreach' actually work?Why shouldn't I use mysql_* functions in PHP?

Image
Does this AnyDice function accurately calculate the number of ogres you make unconcious with three 4th-level castings of Sleep? Making a sword in the stone, in a medieval world without magic Identifying the interval from A♭ to D♯ Why is Vishnu's skin colour mentioned as both white and blue in the Vishnu-sahasranaama? SQL Server Primary Login Restrictions Have researchers managed to "reverse time"? If so, what does that mean for physics? Why does Deadpool say "You're welcome, Canada," after shooting Ryan Reynolds in the end credits? Check this translation of Amores 1.3.26 Why did it take so long to abandon sail after steamships were demonstrated? Plot a function of two variables equal 0 2D counterpart of std::array in C++17 Could the Saturn V actually have launched astronauts around Venus? Why is "das Weib" grammatically neuter? How to make healing in an exploration game interesting Life insurance that covers only simulta
Read more

Compiling GNU Global with universal-ctags support Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Data science time! April 2019 and salary with experience The Ask Question Wizard is Live!Tags for Emacs: Relationship between etags, ebrowse, cscope, GNU Global and exuberant ctagsVim and Ctags tips and trickscscope or ctags why choose one over the other?scons and ctagsctags cannot open option file “.ctags”Adding tag scopes in universal-ctagsShould I use Universal-ctags?Universal ctags on WindowsHow do I install GNU Global with universal ctags support using Homebrew?Universal ctags with emacsHow to highlight ctags generated by Universal Ctags in Vim?

Image
How to ask rejected full-time candidates to apply to teach individual courses? Why these surprising proportionalities of integrals involving odd zeta values? A German immigrant ancestor has a "Registration Affidavit of Alien Enemy" on file. What does that mean exactly? Why did Israel vote against lifting the American embargo on Cuba? Unix AIX passing variable and arguments to expect and spawn Kepler's 3rd law: ratios don't fit data Why does my GNOME settings mention "Moto C Plus"? Continue tikz picture on next page What documents does someone with a long-term visa need to travel to another Schengen country? “Since the train was delayed for more than an hour, passengers were given a full refund.” – Why is there no article before “passengers”? How to create a command for the "strange m" symbol in latex? How to get a single big right brace? Is the Mordenkainen's Sword spell underpowered? Why did Bronn offer to be Tyrion L
Read more

Add ONERROR event to image from jsp tldHow to add an image to a JPanel?Saving image from PHP URLHTML img scalingCheck if an image is loaded (no errors) with jQueryHow to force an <img> to take up width, even if the image is not loadedHow do I populate hidden form field with a value set in Spring ControllerStyling Raw elements Generated from JSP tagds with Jquery MobileLimit resizing of images with explicitly set width and height attributeserror TLD use in a jsp fileJsp tld files cannot be resolved

Image
At which point does a character regain all their Hit Dice? Why are on-board computers allowed to change controls without notifying the pilots? How do I keep an essay about "feeling flat" from feeling flat? Time travel short story where a man arrives in the late 19th century in a time machine and then sends the machine back into the past How to be diplomatic in refusing to write code that breaches the privacy of our users Understanding "audieritis" in Psalm 94 Greatest common substring Go Pregnant or Go Home Why did Kant, Hegel, and Adorno leave some words and phrases in the Greek alphabet? What to do with wrong results in talks? Failed to fetch jessie backports repository Increase performance creating Mandelbrot set in python How can I replace every global instance of "x[2]" with "x_2" What are the ramifications of creating a homebrew world without an Astral Plane? Are there any comparative studies done between Ashtava
Read more