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)
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
edited Mar 7 at 4:01
BigBen
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
14
|
show 4 more comments
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
edited Mar 7 at 4:01
BigBen
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
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
Numberis defined as a String. InFor Each NumberNumber is an object becauseEachis 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
|
show 4 more comments
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
edited Mar 7 at 4:01
BigBen
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
14
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
edited Mar 7 at 4:01
BigBen
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
14
edited Mar 7 at 4:01
BigBen
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
14
edited Mar 7 at 4:01
BigBen
6,6522719
edited Mar 7 at 4:01
BigBen
6,6522719
edited Mar 7 at 4:01
BigBen
6,6522719
6,6522719
asked Mar 7 at 1:19
Harley CHarley C
14
asked Mar 7 at 1:19
Harley CHarley C
14
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
Numberis defined as a String. InFor Each NumberNumber is an object becauseEachis 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
|
show 4 more comments
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
Numberis defined as a String. InFor Each NumberNumber is an object becauseEachis 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
|
show 4 more comments
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
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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
Numberis defined as a String. InFor Each NumberNumber is an object becauseEachis 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