How can I create a micro to copy each worksheet "TABLE" and paste the tables into another "WORKBOOK" in each "WORKSHEET"

[Corried]

Hello everyone.

I have 185 worksheets. Each worksheet have a table range from column a,b,c,d & e.
Every month the table is updated automatically through data connections etc.

Example:
Sheet #1 row is update to row "5"
Sheet #139 row is updated to "1500"

How can I create a micro to copy each worksheet "TABLE" and paste the tables into another "WORKBOOK" in each "WORKSHEET" starting at column "O" in row "1"
NB: The data should update automatically from the original workbook.

Please view the excel links below.

This workbook is the data I want to copy the "table" Beginning from "Afghanistan" worksheet to "Zimbabwe" worksheet. Total (185).
GDP_Annual_Growth_Rate_%.xlsm

Now

This workbook is where I want to paste the data "table" in each worksheet that have the same name. Pasting on column "O" in row "1". Beginning from "Afghanistan" worksheet to "Zimbabwe" worksheet. Total (182).
NB: This worksheet do not have all the names on the original worksheet.

Unemployment_Rate.xlsm

drive.google.com

.


Can this work?

Thanks in advance

 

[siraj rehman]

Sub copy_sheet_to_new_workbook()
'
' copy_sheet_to_new_workbook Macro
'
' Keyboard Shortcut: Ctrl+n
'
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Sheets("Sheet1").Select
End Sub

try this formula create a new module past this formula there and change sheet name if it's not working to your sheet name

sheet1 to Afghanistan
sheet185 to Zimbabwe

 

[Corried]

Siraj. Thanks for replying to my problem.

I am not sure if I am following your recommendation correctly. I have tried two codes and I have received two errors.

Code #1 Error Name: "Compile error: Syntax error"

Sub copy_sheet_to_new_workbook()
'
' copy_sheet_to_new_workbook Macro
'
' Keyboard Shortcut: Ctrl+n
'
Sheets(Array()).Copy
"Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11", "Sheet12", "Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17", "Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23", "Sheet24", "Sheet25", "Sheet26", "Sheet27", "Sheet28", "Sheet29", "Sheet30", "Sheet31", "Sheet32", "Sheet33", "Sheet34", "Sheet35", "Sheet36", "Sheet37", "Sheet38", "Sheet39", "Sheet40", "Sheet41", "Sheet42", "Sheet43", "Sheet44", "Sheet45", "Sheet46", "Sheet47", "Sheet48", "Sheet49", "Sheet50", "Sheet51", "Sheet52", "Sheet53", "Sheet54", "Sheet55", "Sheet56", "Sheet57", "Sheet58", "Sheet59", "Sheet60", "Sheet61", "Sheet62", "Sheet63", "Sheet64", "Sheet65", "Sheet66", "Sheet67", "Sheet68", "Sheet69", "Sheet70", "Sheet71", "Sheet72", "Sheet73", "Sheet74", "Sheet75", "Sheet76", "Sheet77", "Sheet78", "Sheet79", "Sheet80", "Sheet81", "Sheet82", "Sheet83", "Sheet84", "Sheet85", "Sheet86", "Sheet87", "Sheet88", "Sheet89", "Sheet90", "Sheet91", "Sheet92", "Sheet93", "Sheet94"
, "Sheet95", "Sheet96", "Sheet97", "Sheet98", "Sheet99", "Sheet100", "Sheet101", "Sheet102", "Sheet103", "Sheet104", "Sheet105", "Sheet106", "Sheet107", "Sheet108", "Sheet109", "Sheet110", "Sheet111", "Sheet112", "Sheet113", "Sheet114", "Sheet115", "Sheet116", "Sheet117", "Sheet118", "Sheet119", "Sheet120", "Sheet121", "Sheet122", "Sheet123", "Sheet124", "Sheet125", "Sheet126", "Sheet127", "Sheet128", "Sheet129", "Sheet130", "Sheet131", "Sheet132", "Sheet133", "Sheet134", "Sheet135", "Sheet136", "Sheet137", "Sheet138", "Sheet139", "Sheet140", "Sheet141", "Sheet142", "Sheet143", "Sheet144", "Sheet145", "Sheet146", "Sheet147", "Sheet148", "Sheet149", "Sheet150", "Sheet151", "Sheet152", "Sheet153", "Sheet154", "Sheet155", "Sheet156", "Sheet157", "Sheet158", "Sheet159", "Sheet160", "Sheet161", "Sheet162", "Sheet163", "Sheet164", "Sheet165", "Sheet166", "Sheet167", "Sheet168", "Sheet169", "Sheet170", "Sheet171", "Sheet172", "Sheet173", "Sheet174", "Sheet175", "Sheet176", "Sheet177", "Sheet178", "Sheet179", "Sheet
180", "Sheet181", "Sheet182", "Sheet183", "Sheet184", "Sheet185")).Copy
Sheets("Sheet1").Select
End Sub

Code #2 Error Name: "Compile error: Syntax error"

Sub copy_sheet_to_new_workbook()
'
' copy_sheet_to_new_workbook Macro
'
' Keyboard Shortcut: Ctrl+n
'
Sheets(Array("Afghanistan", "Albania", "Algeria", "Angola", "Argentina", "Armenia", "Australia", "Austria", "Azerbaijan", "Bahamas", "Bahrain", "Bangladesh", "Barbados", "Belarus", "Belgium", "Belize", "Benin", "Bhutan", "Bolivia", "Bosnia_and_Herzegovina", "Botswana", "Brazil", "Brunei", "Bulgaria", "Burkina_Faso", "Burundi", "Cambodia", "Cameroon", "Canada", "Cape Verde", "Cayman_Islands", "Central_African_Republic", "Chad", "Chile", "China", "Colombia", "Comoros", "Congo", "Costa_Rica", "Croatia", "Cuba", "Cyprus", "Czech_Republic", "Denmark", "Djibouti", "Dominican_Republic", "East_Timor", "Ecuador", "Egypt", "El_Salvador", "Equatorial_Guinea", "Eritrea", "Estonia", "Ethiopia", "Euro_Area", "European_Union", "Fiji", "Finland", "France", "Gabon", "Gambia", "Georgia", "Germany", "Ghana", "Greece", "Guatemala", "Guinea", "Guinea_Bissau", "Guyana", "Haiti", "Honduras", "Hong_Kong", "Hungary", "Iceland", "India", "Indonesia", "Iran", "Iraq", "Ireland", "Israel", "Italy", "Ivory_Coast", "Jamaica", "Japan", "Jordan", "Kazakhstan", "Kenya", "Kosovo", "Kuwait", "Kyrgyzstan", "Laos", "Latvia", "Lebanon", "Lesotho", "Liberia", "Libya", "Liechtenstein", "Lithuania", "Luxembourg", "Macau", "Macedonia", "Madagascar", "Malawi", "Malaysia", "Maldives", "Mali", "Malta", "Mauritania", "Mauritius", "Mexico", "Moldova", "Monaco", "Mongolia", "Montenegro", "Morocco", "Mozambique", "Myanmar", "Namibia", "Nepal", "Netherlands", "New_Caledonia", "New_Zealand", "Nicaragua", "Niger", "Nigeria", "North_Korea", "Norway", "Oman", "Pakistan", "Palestine", "Panama", "Papua_New_Guinea", "Paraguay", "Peru", "Philippines", "Poland", "Portugal", "Puerto_Rico", "Qatar", "Republic_of_the_Congo", "Romania", "Russia", "Rwanda", "Sao_Tome_and_Principe", "Saudi_Arabia", "Senegal", "Serbia", "Seychelles", "Sierra_Leone", "Singapore", "Slovakia", "Slovenia", "Somalia", "South_Africa", "South_Korea", "South_Sudan", "Spain", "Sri_Lanka", "Sudan", "Suriname", "Swaziland", "Sweden", "Switzerland", "Syria", "Taiwan", "Tajikistan", "Tanzania", "Thailand", "Togo", "Trinidad_and_Tobago", "Tunisia", "Turkey", "Turkmenistan", "Uganda", "Ukraine", "United_Arab_Emirates", "United_Kingdom", "United_S
tates", "Uruguay", "Uzbekistan", "Venezuela", "Vietnam","Yemen", "Zambia", "Zimbabwe")).Copy
Sheets("Sheet1").Select
End Sub

Please set me to the correct path.

Thanks in advance

Cheers

 

[Corried]

[QUOTE="siraj rehman
[/QUOTE]