Copy all column data from one sheet in a woorkbook & paste onto last row of another column in a different sheet of a different workbook

M

MUJEEBOB

New Member
Joined
Aug 10, 2021
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
I've been struggling to get this for about a week now. Part of my job is compiling time and value data from one workbook (wb.BMBG, sheet.CAD, A6 to end of column and row E) and pasting it into the last rows of two different columns in another workbook (wb.RTRs, sheet.cad, end of data in column A and end of data in column B)
I can sort out the rest of the code given that in code, i can:
1) access the specific sheet in the specific workbook
2) consistently select and copy from A6 (or any other row)
3) paste after the last filled cell in the destination file.
Any help would be highly appreciated
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Welcome to the Board!

I think it would be beneficial for us to see a sample of what this data looks like, and where it is going.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Bloomberg-Realtime Depo Data.xlsx
ABCDEFGH
124/08/2021#########
2Quote Type:Bid
3ON1W
4SFDR1TBidSFDR1Z
5#NAME?TypePriceSize#NAME?TypePriceSize
68/16/2021 2:01:11 AMBID-0.808/16/2021 2:01:10 AMBID-0.80
78/16/2021 3:01:10 AMBID-0.808/16/2021 2:32:59 AMBID-10
88/16/2021 4:01:11 AMBID-0.808/16/2021 3:01:10 AMBID-0.80
98/16/2021 5:01:10 AMBID-0.808/16/2021 3:02:42 AMBID-10
108/16/2021 6:01:11 AMBID-0.808/16/2021 4:01:10 AMBID-0.80
118/16/2021 7:01:09 AMBID-0.808/16/2021 4:34:17 AMBID-10
128/16/2021 8:01:09 AMBID-0.808/16/2021 4:38:22 AMBID-0.80
138/16/2021 9:01:11 AMBID-0.808/16/2021 5:01:10 AMBID-0.80
148/16/2021 10:01:06 AMBID-0.808/16/2021 6:01:11 AMBID-0.80
158/16/2021 10:57:46 AMBID-0.908/16/2021 7:01:09 AMBID-0.80
168/16/2021 11:01:11 AMBID-0.808/16/2021 8:01:10 AMBID-0.80
178/16/2021 12:01:09 PMBID-0.808/16/2021 9:01:11 AMBID-0.80
188/16/2021 1:01:11 PMBID-0.808/16/2021 9:02:41 AMBID-10
198/16/2021 2:01:08 PMBID-0.808/16/2021 9:21:22 AMBID-0.80
208/16/2021 3:01:12 PMBID-0.808/16/2021 9:31:49 AMBID-10
218/16/2021 4:01:08 PMBID-0.808/16/2021 9:38:39 AMBID-0.80
228/16/2021 5:01:13 PMBID-0.808/16/2021 9:39:55 AMBID-0.80
238/16/2021 6:01:09 PMBID-0.808/16/2021 9:40:29 AMBID-0.80
248/16/2021 7:01:09 PMBID-0.808/16/2021 9:40:31 AMBID-0.80
258/16/2021 8:01:08 PMBID-0.808/16/2021 9:41:17 AMBID-0.80
268/16/2021 9:01:11 PMBID-0.808/16/2021 9:42:31 AMBID-0.80
278/16/2021 10:01:09 PMBID-0.808/16/2021 9:43:11 AMBID-0.80
288/16/2021 11:01:10 PMBID-0.808/16/2021 9:44:59 AMBID-0.80
298/16/2021 9:46:29 AMBID-0.80
308/16/2021 9:47:19 AMBID-0.80
318/16/2021 9:50:09 AMBID-0.80
328/16/2021 9:51:33 AMBID-0.80
338/16/2021 9:52:25 AMBID-0.80
348/16/2021 9:56:10 AMBID-0.80
358/16/2021 9:56:52 AMBID-0.80
368/16/2021 9:58:36 AMBID-0.80
378/16/2021 10:01:06 AMBID-0.80
388/16/2021 10:01:10 AMBID-0.80
398/16/2021 10:01:46 AMBID-0.80
408/16/2021 10:31:39 AMBID-0.80
418/16/2021 10:33:15 AMBID-0.80
428/16/2021 10:34:05 AMBID-0.80
438/16/2021 10:34:57 AMBID-0.80
448/16/2021 10:36:10 AMBID-0.80
458/16/2021 10:36:46 AMBID-0.80
468/16/2021 10:40:28 AMBID-0.80
478/16/2021 10:41:12 AMBID-0.80
488/16/2021 10:42:00 AMBID-0.80
498/16/2021 10:43:40 AMBID-0.80
508/16/2021 10:44:12 AMBID-0.80
518/16/2021 10:44:48 AMBID-0.80
528/16/2021 10:45:11 AMBID-0.90
538/16/2021 10:45:11 AMBID-0.80
548/16/2021 10:45:11 AMBID-0.80
558/16/2021 10:45:30 AMBID-0.80
568/16/2021 10:46:14 AMBID-0.80
578/16/2021 10:47:26 AMBID-0.80
588/16/2021 10:48:52 AMBID-0.80
598/16/2021 10:49:26 AMBID-0.80
608/16/2021 10:50:24 AMBID-0.80
618/16/2021 10:50:57 AMBID-0.80
628/16/2021 10:51:14 AMBID-0.80
638/16/2021 10:52:00 AMBID-0.80
648/16/2021 10:54:22 AMBID-0.80
658/16/2021 10:54:35 AMBID-0.80
668/16/2021 10:55:10 AMBID-0.90
678/16/2021 10:55:54 AMBID-0.90
688/16/2021 10:56:44 AMBID-0.80
698/16/2021 10:57:46 AMBID-0.90
708/16/2021 10:57:52 AMBID-0.80
718/16/2021 10:57:59 AMBID-0.90
728/16/2021 10:58:22 AMBID-0.80
738/16/2021 10:59:06 AMBID-0.80
748/16/2021 11:00:34 AMBID-0.80
758/16/2021 11:01:11 AMBID-0.80
768/16/2021 11:01:34 AMBID-0.80
778/16/2021 11:03:12 AMBID-0.80
788/16/2021 11:04:10 AMBID-0.80
798/16/2021 11:04:54 AMBID-0.80
808/16/2021 11:05:38 AMBID-0.80
818/16/2021 11:06:30 AMBID-0.80
828/16/2021 11:08:38 AMBID-0.90
838/16/2021 11:09:16 AMBID-0.80
848/16/2021 11:10:00 AMBID-0.80
858/16/2021 11:10:56 AMBID-0.80
868/16/2021 11:11:44 AMBID-0.80
878/16/2021 11:12:20 AMBID-0.80
888/16/2021 11:13:02 AMBID-0.80
898/16/2021 11:13:40 AMBID-0.80
908/16/2021 11:14:22 AMBID-0.90
918/16/2021 11:15:58 AMBID-0.80
928/16/2021 11:17:24 AMBID-0.80
938/16/2021 11:19:14 AMBID-0.80
948/16/2021 11:19:58 AMBID-0.80
958/16/2021 11:20:40 AMBID-0.80
968/16/2021 11:23:53 AMBID-0.80
978/16/2021 11:25:14 AMBID-0.80
988/16/2021 11:25:56 AMBID-0.80
998/16/2021 11:26:27 AMBID-0.80
1008/16/2021 11:27:01 AMBID-0.80
1018/16/2021 11:30:25 AMBID-0.80
1028/16/2021 11:31:13 AMBID-0.80
1038/16/2021 11:32:27 AMBID-0.80
1048/16/2021 11:33:09 AMBID-0.80
1058/16/2021 11:36:57 AMBID-0.80
1068/16/2021 11:37:49 AMBID-0.80
1078/16/2021 11:38:45 AMBID-0.80
1088/16/2021 11:39:45 AMBID-0.80
1098/16/2021 11:41:19 AMBID-0.80
1108/16/2021 11:41:51 AMBID-0.80
1118/16/2021 11:42:37 AMBID-0.80
1128/16/2021 11:43:19 AMBID-0.80
1138/16/2021 11:44:13 AMBID-0.90
1148/16/2021 11:44:43 AMBID-0.80
1158/16/2021 11:45:33 AMBID-0.80
1168/16/2021 11:49:17 AMBID-0.80
1178/16/2021 11:51:03 AMBID-0.90
1188/16/2021 11:51:47 AMBID-0.90
1198/16/2021 11:52:33 AMBID-0.80
1208/16/2021 12:00:38 PMBID-0.80
1218/16/2021 12:01:09 PMBID-0.80
1228/16/2021 12:07:33 PMBID-0.80
1238/16/2021 12:08:01 PMBID-0.90
1248/16/2021 12:08:39 PMBID-0.90
1258/16/2021 12:09:31 PMBID-0.80
1268/16/2021 12:11:59 PMBID-0.80
1278/16/2021 12:12:39 PMBID-0.80
1288/16/2021 12:13:21 PMBID-0.90
1298/16/2021 12:14:05 PMBID-0.90
1308/16/2021 12:14:53 PMBID-0.80
1318/16/2021 12:18:41 PMBID-0.80
1328/16/2021 12:20:43 PMBID-0.80
1338/16/2021 12:22:23 PMBID-0.80
1348/16/2021 12:24:17 PMBID-0.80
1358/16/2021 12:26:03 PMBID-0.80
1368/16/2021 12:26:41 PMBID-0.80
1378/16/2021 12:27:37 PMBID-0.80
1388/16/2021 12:30:58 PMBID-0.80
1398/16/2021 12:31:28 PMBID-0.80
1408/16/2021 12:32:22 PMBID-0.90
1418/16/2021 12:32:52 PMBID-0.80
1428/16/2021 12:33:32 PMBID-0.80
1438/16/2021 12:37:52 PMBID-0.80
1448/16/2021 12:38:30 PMBID-0.80
1458/16/2021 12:40:16 PMBID-0.80
1468/16/2021 12:42:38 PMBID-0.80
1478/16/2021 12:43:34 PMBID-0.90
1488/16/2021 12:44:24 PMBID-0.80
1498/16/2021 12:45:00 PMBID-0.80
1508/16/2021 12:48:12 PMBID-0.80
1518/16/2021 12:49:04 PMBID-0.80
1528/16/2021 12:49:32 PMBID-0.80
1538/16/2021 12:50:12 PMBID-0.80
1548/16/2021 12:50:56 PMBID-0.80
1558/16/2021 12:51:08 PMBID-0.80
1568/16/2021 12:53:18 PMBID-0.80
1578/16/2021 12:53:54 PMBID-0.80
1588/16/2021 12:54:30 PMBID-0.80
1598/16/2021 12:55:22 PMBID-0.80
1608/16/2021 12:56:06 PMBID-0.90
1618/16/2021 12:57:08 PMBID-0.80
1628/16/2021 12:59:08 PMBID-0.80
1638/16/2021 12:59:56 PMBID-0.80
1648/16/2021 1:01:11 PMBID-0.80
1658/16/2021 1:03:02 PMBID-0.80
1668/16/2021 1:04:44 PMBID-0.80
1678/16/2021 1:05:24 PMBID-0.90
1688/16/2021 1:06:04 PMBID-0.80
1698/16/2021 1:06:58 PMBID-0.80
1708/16/2021 1:07:42 PMBID-0.80
1718/16/2021 1:09:00 PMBID-0.80
1728/16/2021 1:13:16 PMBID-0.80
1738/16/2021 1:14:26 PMBID-0.80
1748/16/2021 1:15:12 PMBID-0.80
1758/16/2021 1:16:04 PMBID-0.80
1768/16/2021 1:17:58 PMBID-0.80
1778/16/2021 1:18:30 PMBID-0.80
1788/16/2021 1:21:21 PMBID-0.80
1798/16/2021 1:22:07 PMBID-0.80
1808/16/2021 1:22:47 PMBID-0.90
1818/16/2021 1:23:57 PMBID-0.80
1828/16/2021 1:25:39 PMBID-0.80
1838/16/2021 1:26:53 PMBID-0.80
1848/16/2021 1:27:47 PMBID-0.80
1858/16/2021 1:28:39 PMBID-0.80
1868/16/2021 1:29:19 PMBID-0.80
1878/16/2021 1:30:49 PMBID-0.80
1888/16/2021 1:31:41 PMBID-0.90
1898/16/2021 1:32:29 PMBID-0.80
1908/16/2021 1:35:57 PMBID-0.80
1918/16/2021 1:36:57 PMBID-0.80
1928/16/2021 1:37:47 PMBID-0.80
1938/16/2021 1:38:27 PMBID-0.80
1948/16/2021 1:38:57 PMBID-0.90
1958/16/2021 1:39:31 PMBID-0.80
1968/16/2021 1:41:57 PMBID-0.80
1978/16/2021 1:43:57 PMBID-0.80
1988/16/2021 1:44:45 PMBID-0.80
1998/16/2021 1:45:37 PMBID-0.80
2008/16/2021 1:46:37 PMBID-0.80
2018/16/2021 1:47:21 PMBID-0.80
2028/16/2021 1:48:07 PMBID-0.80
2038/16/2021 1:48:47 PMBID-0.80
2048/16/2021 1:49:25 PMBID-0.80
2058/16/2021 1:50:19 PMBID-0.80
2068/16/2021 1:51:15 PMBID-0.80
2078/16/2021 1:51:45 PMBID-0.80
2088/16/2021 1:53:25 PMBID-0.80
2098/16/2021 1:54:01 PMBID-0.80
2108/16/2021 1:54:31 PMBID-0.80
2118/16/2021 1:55:27 PMBID-0.80
2128/16/2021 1:56:58 PMBID-0.80
2138/16/2021 1:57:36 PMBID-0.80
2148/16/2021 1:58:32 PMBID-0.80
2158/16/2021 2:00:22 PMBID-0.80
2168/16/2021 2:01:08 PMBID-0.90
2178/16/2021 2:01:09 PMBID-0.80
2188/16/2021 2:01:56 PMBID-0.80
2198/16/2021 2:03:42 PMBID-0.80
2208/16/2021 2:04:40 PMBID-0.80
2218/16/2021 2:05:30 PMBID-0.80
2228/16/2021 2:06:42 PMBID-0.80
2238/16/2021 2:07:18 PMBID-0.80
2248/16/2021 2:08:00 PMBID-0.80
2258/16/2021 2:08:50 PMBID-0.80
2268/16/2021 2:10:38 PMBID-0.80
2278/16/2021 2:11:54 PMBID-0.80
2288/16/2021 2:12:50 PMBID-0.80
2298/16/2021 2:13:38 PMBID-0.80
2308/16/2021 2:15:44 PMBID-0.80
2318/16/2021 2:16:22 PMBID-0.80
2328/16/2021 2:17:06 PMBID-0.90
2338/16/2021 2:17:52 PMBID-0.80
2348/16/2021 2:23:43 PMBID-0.80
2358/16/2021 2:24:27 PMBID-0.80
2368/16/2021 2:25:57 PMBID-0.80
2378/16/2021 2:26:41 PMBID-0.80
2388/16/2021 2:28:53 PMBID-0.80
2398/16/2021 2:29:59 PMBID-0.80
2408/16/2021 2:30:55 PMBID-0.80
2418/16/2021 2:31:45 PMBID-0.80
2428/16/2021 2:38:23 PMBID-0.80
2438/16/2021 2:38:53 PMBID-0.80
2448/16/2021 2:40:23 PMBID-0.80
2458/16/2021 2:41:47 PMBID-0.80
2468/16/2021 2:43:05 PMBID-0.90
2478/16/2021 2:44:03 PMBID-0.80
2488/16/2021 2:45:47 PMBID-0.80
2498/16/2021 2:46:17 PMBID-0.80
2508/16/2021 2:48:09 PMBID-0.80
2518/16/2021 2:49:03 PMBID-0.90
2528/16/2021 2:49:41 PMBID-0.80
2538/16/2021 2:50:15 PMBID-0.80
2548/16/2021 2:50:53 PMBID-0.80
2558/16/2021 2:52:23 PMBID-0.80
2568/16/2021 2:53:51 PMBID-0.80
2578/16/2021 2:54:41 PMBID-0.90
2588/16/2021 2:55:19 PMBID-0.80
2598/16/2021 2:57:09 PMBID-0.80
2608/16/2021 2:58:17 PMBID-0.80
2618/16/2021 2:59:09 PMBID-0.80
2628/16/2021 2:59:53 PMBID-0.80
2638/16/2021 3:00:51 PMBID-0.80
2648/16/2021 3:00:57 PMBID-0.80
2658/16/2021 3:01:12 PMBID-0.80
2668/16/2021 3:01:45 PMBID-0.80
CHF
Cell Formulas
RangeFormula
A1A1=TODAY()-1
B1B1=TODAY()
A5A5=BDH(A$4&" Curncy",$B$2,$A$1,$B$1,"Dir=V","IntrRw=true","Headers=Y","Dts=S","cols=4;rows=24")
E5E5=BDH(E$4&" Curncy",$B$2,$A$1,$B$1,"Dir=V","IntrRw=true","Headers=Y","Dts=S","cols=4;rows=705")
Named Ranges
NameRefers ToCells
SpreadsheetBuilder_3=CHF!$A$4:$C$7A5
 
Upvote 0
The one above is the source sheet and the one below is where i have to paste values for weeks and monthly data. I'm sure i can figure it out once i can sort one leg of it. Thanks for the help btw.
Reuters Deal Monitoring-In-House Deposits.xlsm
ABCDEFGHIJKLM
1Overnight1W
2IndexCHFOND=CHFTND=CHFSWD=
3Mean-0.7639-0.83-0.80681
4Standard Deviation0.02079#DIV/0!0.039931
5
6Evaluation Date:#######
7
8#NAME?BID.TIMESTAMPBID.VALUEBID.COUNT#NAME?#NAME?
9CHFOND=CHFOND=CHFOND=CHFTND=CHFTND=CHFTND=CHFSWD=CHFSWD=CHFSWD=
10TimestampBidBid CountTimestampBidBid CountTimestampBidBid Count
118/12/2021 11:01:07 PM-0.76112/08/2021 23:01-0.8318/12/2021 11:01:07 PM-0.771
128/12/2021 12:00:18 AM-0.7618/12/2021 12:01:09 AM-0.771
138/12/2021 1:01:08 AM-0.7618/12/2021 1:01:08 AM-0.771
148/12/2021 1:59:11 AM-0.7618/12/2021 1:59:11 AM-0.771
158/12/2021 2:00:18 AM-0.7618/12/2021 2:01:12 AM-0.771
168/12/2021 3:01:09 AM-0.7618/12/2021 3:01:09 AM-0.771
178/12/2021 4:01:10 AM-0.7618/12/2021 4:01:10 AM-0.771
188/12/2021 5:01:13 AM-0.7618/12/2021 4:35:03 AM-11
198/12/2021 6:01:10 AM-0.7618/12/2021 4:39:07 AM-0.811
208/12/2021 7:01:09 AM-0.7618/12/2021 5:01:12 AM-0.771
218/12/2021 8:01:10 AM-0.7618/12/2021 6:01:11 AM-0.771
228/12/2021 9:01:11 AM-0.7618/12/2021 7:01:09 AM-0.771
238/12/2021 9:59:06 AM-0.7618/12/2021 8:01:10 AM-0.771
248/12/2021 10:01:09 AM-0.7618/12/2021 9:01:09 AM-0.771
258/12/2021 10:58:59 AM-0.8718/12/2021 9:31:50 AM-11
268/12/2021 11:01:09 AM-0.7618/12/2021 9:44:41 AM-0.7951
278/12/2021 12:01:09 PM-0.7618/12/2021 9:45:58 AM-0.81
288/12/2021 1:01:08 PM-0.7618/12/2021 9:46:39 AM-0.7951
298/12/2021 2:01:07 PM-0.7618/12/2021 9:51:57 AM-0.791
308/12/2021 3:01:09 PM-0.7618/12/2021 9:52:01 AM-0.81
318/12/2021 4:01:10 PM-0.7618/12/2021 9:52:06 AM-0.811
328/12/2021 5:01:08 PM-0.7618/12/2021 9:52:55 AM-0.7951
338/12/2021 6:01:08 PM-0.7618/12/2021 9:54:15 AM-0.81
348/12/2021 7:01:10 PM-0.7618/12/2021 9:54:51 AM-0.7951
358/12/2021 8:01:09 PM-0.7618/12/2021 9:55:37 AM-0.81
368/12/2021 9:01:08 PM-0.7618/12/2021 9:56:31 AM-0.7951
378/12/2021 10:01:07 PM-0.7618/12/2021 9:57:29 AM-0.81
388/12/2021 11:01:07 PM-0.7618/12/2021 9:58:11 AM-0.7951
398/12/2021 9:59:06 AM-0.771
408/12/2021 10:01:09 AM-0.771
418/12/2021 10:11:43 AM-0.81
428/12/2021 10:13:09 AM-0.7951
438/12/2021 10:14:39 AM-0.81
448/12/2021 10:15:27 AM-0.7951
458/12/2021 10:16:05 AM-0.81
468/12/2021 10:17:01 AM-0.7951
478/12/2021 10:17:53 AM-0.81
488/12/2021 10:18:31 AM-0.7951
498/12/2021 10:19:23 AM-0.81
508/12/2021 10:20:19 AM-0.7951
518/12/2021 10:22:25 AM-0.81
528/12/2021 10:23:45 AM-0.7951
538/12/2021 10:25:59 AM-0.81
548/12/2021 10:26:37 AM-0.7951
558/12/2021 10:30:08 AM-0.81
568/12/2021 10:30:25 AM-0.811
578/12/2021 10:30:36 AM-0.7951
588/12/2021 10:32:08 AM-0.81
598/12/2021 10:32:46 AM-0.7951
608/12/2021 10:33:54 AM-0.81
618/12/2021 10:35:42 AM-0.8251
628/12/2021 10:36:32 AM-0.81
638/12/2021 10:37:22 AM-0.7951
648/12/2021 10:38:40 AM-0.81
658/12/2021 10:39:10 AM-0.7951
668/12/2021 10:41:38 AM-0.81
678/12/2021 10:42:22 AM-0.7951
688/12/2021 10:42:35 AM-0.81
698/12/2021 10:43:48 AM-0.81
708/12/2021 10:44:40 AM-0.841
718/12/2021 10:45:12 AM-0.851
728/12/2021 10:45:58 AM-0.8551
738/12/2021 10:46:30 AM-0.7951
748/12/2021 10:47:05 AM-0.881
758/12/2021 10:47:05 AM-0.831
768/12/2021 10:47:05 AM-0.831
778/12/2021 10:48:36 AM-0.81
788/12/2021 10:49:18 AM-0.8551
798/12/2021 10:50:04 AM-0.851
808/12/2021 10:50:42 AM-0.7951
818/12/2021 10:52:24 AM-0.81
828/12/2021 10:53:54 AM-0.851
838/12/2021 10:54:50 AM-0.8151
848/12/2021 10:55:48 AM-0.81
858/12/2021 10:56:44 AM-0.7951
868/12/2021 10:58:02 AM-0.81
878/12/2021 10:58:42 AM-0.8251
888/12/2021 10:59:00 AM-0.881
898/12/2021 10:59:42 AM-0.7951
908/12/2021 11:00:06 AM-0.871
91
CHF
Cell Formulas
RangeFormula
C3,L3,H3C3=AVERAGEIF(D11:D1001,1,C11:C1001)
C4,L4,H4C4=STDEV(C11:C1001)
C6C6=Main!C2
A8A8=RHistory(C$2,B8:D8,"START:"&$C$6&" END:"&$C$6& " INTERVAL:TICK",,"CH:In;Fd",B9)
G8,K8G8=RHistory(H2,$B$8:$D$8,"START:"&$C$6&" END:"&$C$6& " INTERVAL:TICK",,"CH:In;Fd",G9)
 
Upvote 0
So for example, overnight data in the bloomberg sheet has to be copied from a6 to the end of A and then pasted in the reuters woorkbook after the last row in the B column.
Furthermore. this data is saved in the sheet CHF in both workbooks. I have to do the same for a number of different sheets. I just need to learn how to access and activate different sheets via VBA code.
 
Upvote 0
I am a little confused by the structure of your data, as your data in columns A and E do not end in the same place on your source file, nor does the data in columns B and G end in the same place on your destination file.

Can you specify exactly which columns are to be copied over from your source file, and to which columns they should be pasted to on your destination file?
 
Upvote 0
Joe4 said:
I am a little confused by the structure of your data, as your data in columns A and E do not end in the same place on your source file, nor does the data in columns B and G end in the same place on your destination file.

Can you specify exactly which columns are to be copied over from your source file, and to which columns they should be pasted to on your destination file?
Click to expand...
pardon me. the original post had a typo. the rates vary so the columns often do not line up (towards the end).
hence i'm having trouble writing up the code for the last filled cell and lining up/activating the worksheets and it makes a mess of things.

The Items from column A (A6 to the last filled row) and column C (C6 to the last filled row need to be copied from the source WB and pasted in the destination WB after the last filled cell in Column B and C respectively. in addition, i usually click on the corner of the last filled column D to autofill the "bid counts".
then E6:E & G6:G (from the source sheet) to The last filled cells in G & H (in the destination sheet) respectively.
(the same is done for another 50 columns but i'll cross that bridge when i get there.)
 
Upvote 0
Is there a column which usually reaches further than the other columns?
Or is there roughly the same amount of misaligned cells at the bottom?
 
Upvote 0
Dav3yRox said:
Is there a column which usually reaches further than the other columns?
Or is there roughly the same amount of misaligned cells at the bottom?
Click to expand...
not the same but definitely not more. sometimes its empty columns that need to be filled.
 
Upvote 0
OK, I came up with something that I think you should be able to adapt for your needs.

It is assuming that you have the two file you need open already (if not, you could add VBA code to open them up, and can probably get a lot of that code using the Macro Recorder). It is also assuming that either there is only one sheet in each workbook (or the sheet you want to work off of is the active one). If that is not the case, you can easily add sheet references to the code.
VBA Code: 
Here is what the code looks like:
Sub MyCopy()

    Dim wbS As Workbook
    Dim wbD As Workbook
    Dim lrS As Long
    Dim frD As Long
    Dim lrD As Long
    
    Application.ScreenUpdating = False
    
'   Set source and destination workbooks
    Windows("Book1.xlsm").Activate
    Set wbS = ActiveWorkbook
    Windows("Book2.xlsm").Activate
    Set wbD = ActiveWorkbook
    
'***BLOCK1***
'   Find last row for column A in source file
    wbS.Activate
    lrS = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Find first available row in column B in destination file
    wbD.Activate
    frD = Cells(Rows.Count, "B").End(xlUp).Row + 1
    
'   Copy data from column A of source file to column B of destination file
    wbS.Activate
    Range("A6:A" & lrS).Copy
    wbD.Activate
    Range("B" & frD).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Copy data from column C of source file to column C of destination file
    wbS.Activate
    Range("C6:C" & lrS).Copy
    wbD.Activate
    Range("C" & frD).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Find last row in destination file in column C
    lrD = Cells(Rows.Count, "C").End(xlUp).Row
    
'   Copy column D down for all rows in destination file
    Cells(frD - 1, "D").Copy Range(Cells(frD, "D"), Cells(lrD, "D"))
    
'***END BLOCK1***

    Application.ScreenUpdating = True
    
End Sub
You will need to change the file names ("Book1.xlsm" and "Book2.xlsm") to match your file names.
Also, my code only does copy from columns A and C from the one sheet to columns A and B of the other sheet, then copy column D down.
Since the second part (copying for columns E and H) is the exact same process as the first, I left that part for you to do.
All you have to do is copy the part of my code, from "***BLOCK1" to "***END BLOCK1" and paste it under the first block, and change the column references to work on the second block.
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
186
Insurance Dave
I
A
  • Question
Copy & Paste from workbook to another with criterias
Replies
2
Views
118
Anthony86
A
P
  • Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
88
MountainFog
M
B
  • Solved
vba code line to select to the last row of data in 3 different columns
Replies
3
Views
117
dreid1011
dreid1011
P
  • Question
Need VBA to automatically copy paste rows based on dates of current month.
2
Replies
11
Views
175
pk970
P

Forum statistics

Threads
1,214,388
Messages
6,119,226
Members
448,878
Latest member
Da9l87

Share this page

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