Copy and Paste Values from 1 Worksheet to Another Based on Column Criteria

aroig07

New Member
Joined
Feb 26, 2019
Messages
37
Hello there !!!! I am new to excel and trying to do a pretty big project and I cannot figure out how to solve an error that keeps popping up. I have a weekly schedule which has 7 columns, each one for a day of the week. I am now trying to do a daily schedule which copies all the values from the column pertaining to today's date. I have written this code, but I think I am not using the LastColumn as I should. Please help if possible and thank you in advance !!!

Here is the code I have written up until now:

Sub DailySchedule()

Application.ScreenUpdating = False


Dim todaydate As Date 'variable for todays date
Dim WeeklySchedule As Worksheet 'where is the data copied from
Dim DailySchedule As Worksheet 'where is the data pasted to
Dim LastColumn As Integer 'last column in weekly schedule sheet


'set variables of worksheets and ranges
Set WeeklySchedule = Sheets("Weekly Schedule")
Set DailySchedule = Sheets("Daily Schedule")
todaydate = Format(Date, "m/d/yyyy")


'clear old data from the master schedule sheet
DailySchedule.Range("B4:B1000").ClearContents


'go to weekly schedule and start searching on row 4 for the date and copying if matches today's date
WeeklySchedule.Visible = True
WeeklySchedule.Select
LastColumn = Cells(4, Rows.Count).End(xlUp).Row


'loop through the columns to find the matching records
For i = 2 To LastColumn
If Cells(4, i) = todaydate Then 'if the date matches todays date then copy the values
Range(Cells(1, i), Cells(1, i)).Copy 'copy column
DailySchedule.Select 'go to the daily schedule sheet
Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 'find first blank row and paste
WeeklySchedule.Select 'go back to the weekly schedule sheet and continue searching
End If
Next i


DailySchedule.Select 'so that the daily schedule is selected when the procedure ends
WeeklySchedule.Visible = False


End Sub
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,917
Office Version
  1. 365
Platform
  1. Windows
Try
Code:
LastColumn = Cells(4, Columns.Count).End(xlToLeft).Column
 

aroig07

New Member
Joined
Feb 26, 2019
Messages
37
I was just going to post an update with that exact code, but its still not copying. Not showing an error, but also not copying the data and pasting it.

Sub DailySchedule()


Application.ScreenUpdating = False


Dim todaydate As Date 'variable for todays date
Dim WeeklySchedule As Worksheet 'where is the data copied from
Dim DailySchedule As Worksheet 'where is the data pasted to
Dim LastColumn As Integer 'last column in weekly schedule sheet


'set variables of worksheets and ranges
Set WeeklySchedule = Sheets("Weekly Schedule")
Set DailySchedule = Sheets("Daily Schedule")
todaydate = Format(Date, "m/d/yyyy")


'clear old data from the master schedule sheet
DailySchedule.Range("B4:B1000").ClearContents


'go to weekly schedule and start searching on row 4 for the date and copying if matches today's date
WeeklySchedule.Visible = True
WeeklySchedule.Select
LastColumn = Cells(4, Columns.Count).End(xlToLeft).Column


'loop through the columns to find the matching records
For i = 2 To LastColumn
If Cells(4, i) = todaydate Then 'if the date matches todays date then copy the values
Range(Cells(1, i), Cells(1, i)).Copy 'copy column
DailySchedule.Select 'go to the daily schedule sheet
Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 'find first blank row and paste
WeeklySchedule.Select 'go back to the weekly schedule sheet and continue searching
End If
Next i


DailySchedule.Select 'so that the daily schedule is selected when the procedure ends


End Sub
 

aroig07

New Member
Joined
Feb 26, 2019
Messages
37
FIXED !!! Just had to change the values in the range because it was only copying cell 1.


Sub DailySchedule()


Application.ScreenUpdating = False


Dim todaydate As Date 'variable for todays date
Dim WeeklySchedule As Worksheet 'where is the data copied from
Dim DailySchedule As Worksheet 'where is the data pasted to
Dim LastColumn As Integer 'last column in weekly schedule sheet


'set variables of worksheets and ranges
Set WeeklySchedule = Sheets("Weekly Schedule")
Set DailySchedule = Sheets("Daily Schedule")
todaydate = Format(Date, "m/d/yyyy")


'clear old data from the master schedule sheet
DailySchedule.Range("B4:B1000").ClearContents


'go to weekly schedule and start searching on row 4 for the date and copying if matches today's date
WeeklySchedule.Visible = True
WeeklySchedule.Select
LastColumn = Cells(4, Columns.Count).End(xlToLeft).Column


'loop through the columns to find the matching records
For i = 2 To LastColumn
If Cells(4, i) = todaydate Then 'if the date matches todays date then copy the values
Range(Cells(1, i), Cells(1000, i)).Copy 'copy column
DailySchedule.Select 'go to the daily schedule sheet
Range("B1000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulasAndNumberFormats 'find first blank row and paste
WeeklySchedule.Select 'go back to the weekly schedule sheet and continue searching
End If
Next i


DailySchedule.Select 'so that the daily schedule is selected when the procedure ends


End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,917
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,466
Members
414,069
Latest member
StudExcel

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