VBA: Copy and paste next available column to a specific sheet pending on value of cell.

Gmonzie

New Member
Joined
Apr 7, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hey guys,

New to VBA so struggling here.

1617819652960.png


I want to copy and paste data to a specific sheet (paste onto the next available column) depending on the value of a cell. Ex. If J6 says "Broadway", I want to copy and paste J6:J33 to sheet named "Broadway" to the next available column. Any help would be much appreciated.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
into row 6 again?

VBA Code:
Sub copytonext()

Dim lastcol As Long
Dim ws As Worksheet
Dim wsdname As String
Dim wsdest As Worksheet

Set ws = ActiveSheet
wsdname = ws.Range("J6")
Set wsdest = Sheets(wsdname)
lastcol = wsdest.Cells(6, wsdest.Columns.Count).End(xlToLeft).Column

ws.Range("J6:J33").Copy wsdest.Cells(6, lastcol + 1)


End Sub
 
Upvote 0
into row 6 again?

VBA Code:
Sub copytonext()

Dim lastcol As Long
Dim ws As Worksheet
Dim wsdname As String
Dim wsdest As Worksheet

Set ws = ActiveSheet
wsdname = ws.Range("J6")
Set wsdest = Sheets(wsdname)
lastcol = wsdest.Cells(6, wsdest.Columns.Count).End(xlToLeft).Column

ws.Range("J6:J33").Copy wsdest.Cells(6, lastcol + 1)


End Sub
Hi! Thank you for your help! What do you mean by "into row 6 again?" I have multiple sheets. The screenshot below is my first sheet (cover sheet if you will) named Data Entry:
1617890053754.png

Following, I have 10 sheets that is respective to the drop down list in J6. ex, list shows: Broadway, Main St, Chambers, etc and have sheets named Broadway, Main St, Chambers, etc. If J6 has the word "Main St" then I want the data in J6:J33 to be pasted (values only) into sheet called "Main St" to the next available column.

I tried to use this code but had a bug error. Thank you!
 
Upvote 0
Hi ! As a beginner starter :​
VBA Code:
Sub Demo1()
    If Evaluate("(ISREF('" & [J6].Text & "'!A1)") Then
        With Sheets([J6].Text).UsedRange
            .Cells(1, .Columns.Count + 1).Resize(28).Value = [J6:J33].Value
        End With
    End If
End Sub
 
Upvote 0
There is a typo in the Evaluate formula, it must be without the red char "(ISREF('" so "ISREF('" …​
 
Upvote 0
What was the error? Into row 6 again as in pasting into. You didn't specify the row, just the next column.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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