Copy ranges from one workbook to another The Next CEO of Stack OverflowMacro to copy Range to a new WorkbookReferencing temporary, unsaved workbook when copying worksheetsCopy range from multiple worksheet to a single worksheetCreate new workbook from existing worksheetVBA Error Subscript out of range in for loop used to copy/pasteCopy sheets from different workbooks into a single workbookExcel VBA Copy Range Transpose from Another SpreadsheetCopy Named Range Data of the workbook to Another workbook by VBACopy/SpecialPaste Range from multiple closed workbooks to master workbookCopy Ranges from one workbook to another workbook
How to write papers efficiently when English isn't my first language?
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
Unreliable Magic - Is it worth it?
If the heap is initialized for security, then why is the stack uninitialized?
When airplanes disconnect from a tanker during air to air refueling, why do they bank so sharply to the right?
Only print output after finding pattern
Text adventure game code
Was a professor correct to chastise me for writing "Prof. X" rather than "Professor X"?
Why doesn't a table tennis ball float on the surface? How do we calculate buoyancy here?
Should I tutor a student who I know has cheated on their homework?
Why didn't Khan get resurrected in the Genesis Explosion?
How to use tikz in fbox?
Is HostGator storing my password in plaintext?
Can the Reverse Gravity spell affect the Meteor Swarm spell?
Science fiction (dystopian) short story set after WWIII
How to get regions to plot as graphics
Term for the "extreme-extension" version of a straw man fallacy?
How to write the block matrix in LaTex?
What does "Its cash flow is deeply negative" mean?
How to Reset Passwords on Multiple Websites Easily?
How can I open an app using Terminal?
How did people program for Consoles with multiple CPUs?
Why did we only see the N-1 starfighters in one film?
Customer Requests (Sometimes) Drive Me Bonkers!
Copy ranges from one workbook to another
The Next CEO of Stack OverflowMacro to copy Range to a new WorkbookReferencing temporary, unsaved workbook when copying worksheetsCopy range from multiple worksheet to a single worksheetCreate new workbook from existing worksheetVBA Error Subscript out of range in for loop used to copy/pasteCopy sheets from different workbooks into a single workbookExcel VBA Copy Range Transpose from Another SpreadsheetCopy Named Range Data of the workbook to Another workbook by VBACopy/SpecialPaste Range from multiple closed workbooks to master workbookCopy Ranges from one workbook to another workbook
I have a workbook with three sheets. RC,RI,RCB. I need To write VBA code, which will copy ranges from this workbook and will paste to a newly created workbook.
In this code, everything Works except sheet RCB. Debug turns on on line 12
Sub Budget()
With Workbooks.Add
ActiveSheet.Name = "RC"
ThisWorkbook.Worksheets("RC").Range("A:D").Copy .Worksheets("RC").Range("A1")
ThisWorkbook.Worksheets("RC").Range("E:G").Copy .Worksheets("RC").Range("E1")
Sheets.Add
ActiveSheet.Name = "RI"
ThisWorkbook.Worksheets("RI").Range("A:E").Copy .Worksheets("RI").Range("A1")
ThisWorkbook.Worksheets("RI").Range("G:G").Copy .Worksheets("RI").Range("G1")
Sheets.Add
ActiveSheet.Name = "RCB"
ThisWorkbook.Worksheets("RCB").Range("A:C").Copy .Worksheets("RCB").Range("A1")
ThisWorkbook.Worksheets("RCB").Range("E:E").Copy .Worksheets("RCB").Range("G1")
.SaveAs "C:UserslomidDesktop4finansi3"
.Close
End With
End Sub
I think it has to be about active sheet and workbook.
this is an error it gives me: "Run-time error 9: subscript out of range"
excel vba
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
|
show 2 more comments
I have a workbook with three sheets. RC,RI,RCB. I need To write VBA code, which will copy ranges from this workbook and will paste to a newly created workbook.
In this code, everything Works except sheet RCB. Debug turns on on line 12
Sub Budget()
With Workbooks.Add
ActiveSheet.Name = "RC"
ThisWorkbook.Worksheets("RC").Range("A:D").Copy .Worksheets("RC").Range("A1")
ThisWorkbook.Worksheets("RC").Range("E:G").Copy .Worksheets("RC").Range("E1")
Sheets.Add
ActiveSheet.Name = "RI"
ThisWorkbook.Worksheets("RI").Range("A:E").Copy .Worksheets("RI").Range("A1")
ThisWorkbook.Worksheets("RI").Range("G:G").Copy .Worksheets("RI").Range("G1")
Sheets.Add
ActiveSheet.Name = "RCB"
ThisWorkbook.Worksheets("RCB").Range("A:C").Copy .Worksheets("RCB").Range("A1")
ThisWorkbook.Worksheets("RCB").Range("E:E").Copy .Worksheets("RCB").Range("G1")
.SaveAs "C:UserslomidDesktop4finansi3"
.Close
End With
End Sub
I think it has to be about active sheet and workbook.
this is an error it gives me: "Run-time error 9: subscript out of range"
excel vba
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
2
Are you sure thatThisWorkbookcontains a sheetRCB?
– FunThomas
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
1
OP is copying data fromThisWorkbook-sheets to sheets created in a new Workbook.Workbooks.Addnot only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.
– FunThomas
Mar 7 at 14:48
1
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51
|
show 2 more comments
I have a workbook with three sheets. RC,RI,RCB. I need To write VBA code, which will copy ranges from this workbook and will paste to a newly created workbook.
In this code, everything Works except sheet RCB. Debug turns on on line 12
Sub Budget()
With Workbooks.Add
ActiveSheet.Name = "RC"
ThisWorkbook.Worksheets("RC").Range("A:D").Copy .Worksheets("RC").Range("A1")
ThisWorkbook.Worksheets("RC").Range("E:G").Copy .Worksheets("RC").Range("E1")
Sheets.Add
ActiveSheet.Name = "RI"
ThisWorkbook.Worksheets("RI").Range("A:E").Copy .Worksheets("RI").Range("A1")
ThisWorkbook.Worksheets("RI").Range("G:G").Copy .Worksheets("RI").Range("G1")
Sheets.Add
ActiveSheet.Name = "RCB"
ThisWorkbook.Worksheets("RCB").Range("A:C").Copy .Worksheets("RCB").Range("A1")
ThisWorkbook.Worksheets("RCB").Range("E:E").Copy .Worksheets("RCB").Range("G1")
.SaveAs "C:UserslomidDesktop4finansi3"
.Close
End With
End Sub
I think it has to be about active sheet and workbook.
this is an error it gives me: "Run-time error 9: subscript out of range"
excel vba
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
I have a workbook with three sheets. RC,RI,RCB. I need To write VBA code, which will copy ranges from this workbook and will paste to a newly created workbook.
In this code, everything Works except sheet RCB. Debug turns on on line 12
Sub Budget()
With Workbooks.Add
ActiveSheet.Name = "RC"
ThisWorkbook.Worksheets("RC").Range("A:D").Copy .Worksheets("RC").Range("A1")
ThisWorkbook.Worksheets("RC").Range("E:G").Copy .Worksheets("RC").Range("E1")
Sheets.Add
ActiveSheet.Name = "RI"
ThisWorkbook.Worksheets("RI").Range("A:E").Copy .Worksheets("RI").Range("A1")
ThisWorkbook.Worksheets("RI").Range("G:G").Copy .Worksheets("RI").Range("G1")
Sheets.Add
ActiveSheet.Name = "RCB"
ThisWorkbook.Worksheets("RCB").Range("A:C").Copy .Worksheets("RCB").Range("A1")
ThisWorkbook.Worksheets("RCB").Range("E:E").Copy .Worksheets("RCB").Range("G1")
.SaveAs "C:UserslomidDesktop4finansi3"
.Close
End With
End Sub
I think it has to be about active sheet and workbook.
this is an error it gives me: "Run-time error 9: subscript out of range"
excel vba
excel vba
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
edited Mar 7 at 15:00
Pᴇʜ
24.8k63052
24.8k63052
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
asked Mar 7 at 14:24
giorgi lomidzegiorgi lomidze
127
127
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
2
Are you sure thatThisWorkbookcontains a sheetRCB?
– FunThomas
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
1
OP is copying data fromThisWorkbook-sheets to sheets created in a new Workbook.Workbooks.Addnot only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.
– FunThomas
Mar 7 at 14:48
1
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51
|
show 2 more comments
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
2
Are you sure thatThisWorkbookcontains a sheetRCB?
– FunThomas
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
1
OP is copying data fromThisWorkbook-sheets to sheets created in a new Workbook.Workbooks.Addnot only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.
– FunThomas
Mar 7 at 14:48
1
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
2
2
Are you sure that
ThisWorkbook contains a sheet RCB ?– FunThomas
Mar 7 at 14:41
Are you sure that
ThisWorkbook contains a sheet RCB ?– FunThomas
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
1
1
OP is copying data from
ThisWorkbook-sheets to sheets created in a new Workbook. Workbooks.Add not only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.– FunThomas
Mar 7 at 14:48
OP is copying data from
ThisWorkbook-sheets to sheets created in a new Workbook. Workbooks.Add not only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.– FunThomas
Mar 7 at 14:48
1
1
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51
|
show 2 more comments
1 Answer
1
active
oldest
votes
You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.
Application.DisplayAlerts = False
With ThisWorkbook
.Sheets("RC").Range("H:Z").EntireColumn.Delete
.Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
.Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete
.SaveAs "C:UserslomidDesktop4finansi3.xlsx"
End With
Application.DisplayAlerts = True
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
add a comment |
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%2f55046102%2fcopy-ranges-from-one-workbook-to-another%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.
Application.DisplayAlerts = False
With ThisWorkbook
.Sheets("RC").Range("H:Z").EntireColumn.Delete
.Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
.Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete
.SaveAs "C:UserslomidDesktop4finansi3.xlsx"
End With
Application.DisplayAlerts = True
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
add a comment |
You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.
Application.DisplayAlerts = False
With ThisWorkbook
.Sheets("RC").Range("H:Z").EntireColumn.Delete
.Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
.Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete
.SaveAs "C:UserslomidDesktop4finansi3.xlsx"
End With
Application.DisplayAlerts = True
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
add a comment |
You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.
Application.DisplayAlerts = False
With ThisWorkbook
.Sheets("RC").Range("H:Z").EntireColumn.Delete
.Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
.Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete
.SaveAs "C:UserslomidDesktop4finansi3.xlsx"
End With
Application.DisplayAlerts = True
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
You can dispense with all the copy/paste. When you use SaveAs to save your current workbook, the original workbook will close without saving and the new workbook will be open. From what i can tell from your question you want to keep only certain columns. I like to delete the columns you don't want and do a SaveAs to a new workbook name. The code below is much cleaner. Use Application.DisplayAlerts = False so you don't get the The following features cannot be saved in macro-free workbooks: prompt.
Application.DisplayAlerts = False
With ThisWorkbook
.Sheets("RC").Range("H:Z").EntireColumn.Delete
.Sheets("RI").Range("H:Z, F:F").EntireColumn.Delete
.Sheets("RCB").Range("F:Z, D:D").EntireColumn.Delete
.SaveAs "C:UserslomidDesktop4finansi3.xlsx"
End With
Application.DisplayAlerts = True
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
answered Mar 7 at 19:25
GMalcGMalc
1,2221411
1,2221411
add a comment |
add a comment |
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%2f55046102%2fcopy-ranges-from-one-workbook-to-another%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
What does the debug tell you?
– Darrell H
Mar 7 at 14:34
2
Are you sure that
ThisWorkbookcontains a sheetRCB?– FunThomas
Mar 7 at 14:41
Run-time error 9: subscript out of range
– giorgi lomidze
Mar 7 at 14:41
1
OP is copying data from
ThisWorkbook-sheets to sheets created in a new Workbook.Workbooks.Addnot only creates a new workbook, but sets it active. The code could be written (much) better, but this is not the issue. I did a quick test and it worked for me - assuming that the original workbook has all three sheets.– FunThomas
Mar 7 at 14:48
1
@SJR - Ohhh, okay I see. This is confusing to read to me...I'd suggest OP instead declare the workbooks properly instead...
– BruceWayne
Mar 7 at 14:51