VBA Help Needed: Match/Find dates across 2 workbooks, loop for different sheets

lenticular

New Member
Joined
Sep 10, 2014
Messages
3
Hi there! I just started learning VBA yesterday and could use some help....
I have two workbooks: one "sept" workbook with a sheet of data per day for 7 stations (for a total of 30 sheets), and one "Stn" workbook with 7 sheets, one per station, with each sheet containing a large list of dates.
I want to take data for a given day (1 sheet) for all 7 stations from "sept", and paste it into the corresponding cells for that day in the "stn", into each respective station's sheet.
So far, I've made a code that can do this if I tell it exactly which cell corresponds to the date that I am pasting from. What I'm trying to do is make it match or find the date itself (dates are in yyyy-mm-dd format for both sheets) based on a cell in each sheet in "sept" that has the date, and paste the data into the neighbouring cell of the corresponding date in the stn workbook. And then I want to try and make it loop so it can do that for each of the 30 days....

Here is what I have so far:

Code:
Sub LoopSept()
Windows("sept.xlsm").Activate
Dim st As Worksheet 'This is me trying to simplify things for the loop
For Each st In Sheets 'This is me trying to make it loop, work in progress
    Windows("sept.xlsm").Activate
    st.Range("A1:R183").ClearFormats 'When I've gotten it to work, instead of st, I had ActiveSheet
    st.Range("B49:O51").Copy 'This is data from Sept that I want to paste into the first station's sheet in stn
    Windows("Stn.xlsx").Activate
    Worksheets("CYVR").Activate
    ActiveSheet.Range("C5").Activate 'C5 corresponds to the date where I wanted it to paste, however I manually determined that, this is the part I want to get it to find the corresponding date. In the future, it would use range("B30") of sept to match to dates in column B of the stn sheet
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate 
    st.Range("B71:O73").Copy 'This is data from sept that I want to paste into the 2nd station's sheet in stn
    Windows("Stn.xlsx").Activate
    Worksheets("CYYJ").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate
    ActiveSheet.Range("B93:O95").Copy
    Windows("Stn.xlsx").Activate
    Worksheets("CYXX").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate
    ActiveSheet.Range("B115:O117").Copy
    Windows("Stn.xlsx").Activate
    Worksheets("CWSK").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate
    ActiveSheet.Range("B137:O139").Copy
    Windows("Stn.xlsx").Activate
    Worksheets("CVOC").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate
    ActiveSheet.Range("B159:O161").Copy
    Windows("Stn.xlsx").Activate
    Worksheets("CYQQ").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

    Windows("sept.xlsm").Activate
    ActiveSheet.Range("B181:O183").Copy
    Windows("Stn.xlsx").Activate
    Worksheets("CWQC").Activate
    ActiveSheet.Range("C5").Activate
    ActiveSheet.Paste

Application.CutCopyMode = False
Next rs 'this is again me trying to loop....still a work in progress
End Sub
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,621
Members
414,082
Latest member
sasmita

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
Top